[Conny Westh]

Jag har gjort en SP som räknar ut medianvärdet i en kolumn i MySQL v 5.1.53 men får inte AVG() elelr SUM() att rulla när jag använder PREPARE/EXECUTE i en SP.

Följande kod fungerar som den ska men är ingen sngg lösning då jag var tvungen att köra två SQL-satser efetr varandra när man har ett jämt antal poster.

Idealet hade varit att kunna använda SELECT AVG(age) ... i stället för att jag själv manuellt måste räkna ut medelvärdet vid jämnt antal poster.

Tabelldefinitioner:

delimiter $$

CREATE TABLE `name` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=latin1$$


delimiter $$

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1$$


DELIMITER //
//


delimiter ;

SP för att lägga till Personer och namn:

DELIMITER //
//
DROP PROCEDURE IF EXISTS addPerson;
//
delimiter //
create procedure addPerson(Namn tinytext, age int(11))
begin
  declare chk int default 0;
  select count(*) from Name where Name = Namn into chk;
  if chk = 0 then
    insert into Name (Name) values (Namn);
    insert into Person (Name, age) values (last_insert_id(), age);
  else
    -- Om tanken med proceduren är att man ska kunna lägga till alla personer i 
    -- Persontabellen oavsett om namnet finns sedan tidigare men inga dubbletter
    -- i name-tabellen så ska även denna insert-sats med, kanske bör fler villkor 
    -- byggas till för att det ska bli en vettig funktionalitet.
    insert into Person (Name, age) values ((select id from name where name=Namn), age);
  end if;
end
//

delimiter //

CREATE VIEW `person_view` AS 
select `p`.`id` AS `id`,`n`.`name` AS `name`,`p`.`age` AS `age` 
from (`person` `p` join `name` `n`) 
where (`p`.`name` = `n`.`id`) 
order by `n`.`name`

//



delimiter ;

Testdata:

-- Uppläggning av testdata
CALL addPerson('Nisse', 23);
CALL addPerson('Anders', 34);
CALL addPerson('Barak', 36);
CALL addPerson('Rune', 27);

CALL addPerson('Nisse', 33);
CALL addPerson('Anders', 44);
CALL addPerson('Barak', 46);
CALL addPerson('Rune', 37);

CALL addPerson('Nisse', 43);
CALL addPerson('Anders', 54);
CALL addPerson('Barak', 56);
CALL addPerson('Rune', 47);

SP för att beräkna medianvärdet i en specifik kolumn:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `col_median`(IN tbl varCHAR(64), IN col varCHAR(64))
    READS SQL DATA
    COMMENT 'Selects the average of column col in table tbl'
BEGIN
Declare antal varchar(255);
Declare median varchar(255);
Declare median1 varchar(255);
Declare median2 varchar(255);
Declare stmt_antal varchar(255);
Declare stmt_median varchar(255);
Declare stmt_median1 varchar(255);
Declare stmt_median2 varchar(255);
Declare result_antal integer;
Declare result_median numeric;
Declare result_median1 numeric;
Declare result_median2 numeric;
Declare limit_startpunkt integer;
Declare limit_startpunkt1 integer;
Declare limit_startpunkt2 integer;
Declare limit_antal integer;

SET @antal = CONCAT('SELECT Count(*) INTO @result_antal FROM ' , tbl, ' order by ', col);
PREPARE stmt_antal FROM @antal;
EXECUTE stmt_antal;

-- SELECT @result_antal;
-- DEALOCATE PREPARE stmt_antal;

IF @result_antal mod 2 = 0 THEN
--    select 'Even';
    set @limit_startpunkt1 = (@result_antal DIV 2) -1;
    set @limit_startpunkt2 = (@result_antal DIV 2);
    set @limit_antal=1;
    
    SET @median1 = CONCAT('SELECT ' , col , ' INTO @result_median1 FROM ' , tbl, ' order by ', col, ' LIMIT ', @limit_startpunkt1, ',', @limit_antal);
    PREPARE stmt_median1 FROM @median1;
    EXECUTE stmt_median1;
    
    SET @median2 = CONCAT('SELECT ' , col , ' INTO @result_median2 FROM ' , tbl, ' order by ', col, ' LIMIT ', @limit_startpunkt2, ',', @limit_antal);
    PREPARE stmt_median2 FROM @median2;
    EXECUTE stmt_median2;

    -- SELECT @result_median1;
    -- SELECT @result_median2;
    
    set @result_median := (@result_median1 + @result_median2)/2.0;

    -- SELECT @result_median;
ELSE 
--    select 'Odd';
    set @limit_startpunkt = @result_antal DIV 2;
    set @limit_antal=1;
    SET @median = CONCAT('SELECT ' , col , ' INTO @result_median FROM ' , tbl, ' order by ', col, ' LIMIT ', @limit_startpunkt, ',', @limit_antal);
    PREPARE stmt_median FROM @median;
    EXECUTE stmt_median;
    -- SELECT @result_median;
END IF;
-- select @limit_startpunkt;
-- select @limit_antal;

-- SET @median = CONCAT('SELECT AVG(' , col , ') INTO @result_median FROM ' , tbl, ' order by ', col, ' LIMIT ', @limit_startpunkt, ',', @limit_antal);
-- SET @median = CONCAT('SELECT AVG(' , col , ') INTO @result_median FROM ' , tbl, ' order by ', col, ' LIMIT ', @limit_startpunkt, ',', @limit_antal);
-- PREPARE stmt_median FROM @median;
-- EXECUTE stmt_median;

-- SELECT @median;
SELECT @result_median;
-- DEALOCATE PREPARE stmt_median;

END

Inte ens dessa två SQL-satser fungerar som jag tänkt mig:

SELECT AVG(age) FROM person order by age LIMIT 5,2;
SELECT SUM(age) FROM person order by age LIMIT 5,2;

Fär att använda SPn så skriver man så här:

CALL col_median('person','age');