Group the data of a table based on the date and build the multicolumns table for values in MYsql-CodePudding

I was really stuck in this SQL program for more than a week. I have a table that has three columns, one column shows the ids, one shows the date, and one shows the temperature. Here is the code for the table:

Create table  temperature (id int, date varchar(255), temperature float(2, 1));

 insert into temperature (id, date, temperature) values ('1', '2012-01-01 6:00:00',  '8.9');
 insert into temperature (id, date,  temperature) values ('1', '2012-01-01 6:15:00',  '8.5');
 insert into temperature (id, date,  temperature) values ('1', '2012-01-01 6:30:00',  '6.2');
 insert into temperature (id, date,  temperature) values ('1', '2012-01-01 6:45:00',  '8.6');
 insert into temperature(id, date,  temperature) values ('3', '2012-01-02 6:00:00',  '9.1');
 insert into temperature (id, date,  temperature) values ('3', '2012-01-01 6:15:00',  '8.9');
 insert into temperature (id, date,  temperature) values ('4', '2012-01-01 6:00:00',  '8.5');
 insert into temperature (id, date,  temperature) values ('4', '2012-01-01 6:15:00',  '6.2');
 insert into temperature (id, date,  temperature) values ('4', '2012-01-01 6:30:00',  '8.6');
 insert into temperature (id, date,  temperature) values ('2', '2012-01-01 6:00:00',  '9.1');
 SELECT * FROM temperature;

And here is the figure of table:

Each row has a very specific time. I want to show the temperature of each id and each time slot in one column.

For example, for id=1, we have four temperatures. Then we have 4 value and put them in order in the val1-val4. for id=3, we have two day with one numbers, for each day we consider one list, and id=4 we have three numbers in one day and for id=2 we have only one number for one day.

The maximum length of the values is 4.

Finally, I want this table (there is a typo in the figure, val2 for the second id=3 should be 8.9):

I have used the group_concat, however, it saves them as a string. Besides I am using date_format(from_unixtime(date), '%Y-%m-%d')To change the date to YYYY-MM-DD. The code I am using is

   SELECT id, 
          date_format(from_unixtime(date), '%Y-%d-%m') as date, 
          GROUP_CONCAT(value) as Value group by id, date
    FROM temperature;

In my real database, the maximum length of the value is 96 and I have more then 10 Million rows, and it is not efficient to use the CASE and WHEN.

CodePudding user response：

If your MySQL version supports windows function you could use:

with cte as (
select id, date, temperature,row_number() over(partition by id order by `date`) rownum
from temperature
) 
select id,date(`date`),max(case when rownum=1 then cte.temperature else null end) as val1,
                       max(case when rownum=2 then cte.temperature else null end) as val2,
                       max(case when rownum=3 then cte.temperature else null end) as val3,
                       max(case when rownum=4 then cte.temperature else null end) as val4
from cte
group by id,date(`date`);

Demo

CodePudding user response：

With GROUP_CONCAT you can build a list and then change the list to multiple columns.