mysql - SQL 输出每小时的唯一访客

Question

我在 SQL 方面还是个新手，上周在工作中遇到了一个问题。我能够通过 RoR 查询克服这个问题，并根据我的需要重新格式化数据(困惑的 D: )；不过，我真的很想知道sql的解决方案，如果有的话

我有一个像这样的 MySQL 数据库

| id | unique_visitors |      time_period    |
| 1  |              16 | 2013-07-01 00:00:00 |
| 2  |              20 | 2013-07-01 01:00:00 |
| 3  |               2 | 2013-07-01 02:00:00 |

我需要的最终输出如下所示

|    date    | 00 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 |.... | 22 | 23|
| 2013-07-01 | 16 | 20 |  2 | 32 | .........                      

每一行都会显示日期以及每小时的唯一访问者。

我不确定这是否可以通过 sql 查询实现，并且真的很想借此机会学习一些东西。

谢谢 SQL 大师!

Answer 1

CASE 语句会变得很复杂，但你就在这里。 sleep 前我感觉像是一个挑战

表统计信息(id、unique_visitors、time_period)

select DATE_FORMAT(time_period ,'%Y-%m-%d') as date, 
sum(CASE WHEN EXTRACT(hour from time_period)  =  00 THEN unique_visitors ELSE  0 END) as '00', 
sum(CASE WHEN EXTRACT(hour from time_period)  =  01 THEN unique_visitors ELSE  0 END) as '01', 
sum(CASE WHEN EXTRACT(hour from time_period)  =  02 THEN unique_visitors ELSE  0 END) as '02',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  03 THEN unique_visitors ELSE  0 END) as '03',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  04 THEN unique_visitors ELSE  0 END) as '04',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  05 THEN unique_visitors ELSE  0 END) as '05',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  06 THEN unique_visitors ELSE  0 END) as '06',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  07 THEN unique_visitors ELSE  0 END) as '07',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  08 THEN unique_visitors ELSE  0 END) as '08',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  09 THEN unique_visitors ELSE  0 END) as '09',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  10 THEN unique_visitors ELSE  0 END) as '10',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  11 THEN unique_visitors ELSE  0 END) as '11',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  12 THEN unique_visitors ELSE  0 END) as '12',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  13 THEN unique_visitors ELSE  0 END) as '13',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  14 THEN unique_visitors ELSE  0 END) as '14',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  15 THEN unique_visitors ELSE  0 END) as '15',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  16 THEN unique_visitors ELSE  0 END) as '16',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  17 THEN unique_visitors ELSE  0 END) as '17',   
sum(CASE WHEN EXTRACT(hour from time_period)  =  18 THEN unique_visitors ELSE  0 END) as '18',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  19 THEN unique_visitors ELSE  0 END) as '19',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  20 THEN unique_visitors ELSE  0 END) as '20',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  21 THEN unique_visitors ELSE  0 END) as '21',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  22 THEN unique_visitors ELSE  0 END) as '22',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  23 THEN unique_visitors ELSE  0 END) as '23' 
from stats
group by date
order by date

输出

      date  00  01  02  03  04  05  06  07  08  09  10  11  12  13 14  15  16  17  18  19  20  21  22  23
2013-07-01  22  16  2   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
2013-07-02  0   0   0   0   30  0   0   0   50  0   0   0   0   0   0   0   15  0   0   0   0   0   0   0

-杰夫·韦斯