WN - col_median(age)

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:

Kod:

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:

Kod:

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:

Kod:

-- 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:

Kod:

-- --------------------------------------------------------------------------------

-- 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:

Kod:

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:

Kod:

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