MySQL - 将计数函数的结果划分为从单独列派生的列

Question

我最近在 Stack Exchange 的另一个页面上发布了此内容，但相信这是更合适的地方。

好吧，标题看起来有点令人困惑，但我正在努力写下我需要这个查询来做什么，以便最好地解释它。我的数据库中有 3 个表(使用 MySQL Workbench)，但对于这个查询我只是尝试使用一个。名为 service_data 的表具有以下列:

Services_ID|Service_Type|Day|Time|Customer_ID(FK)
1001       |SERVICE1    |Mon|0950|1    
1002       |SERVICE2    |Tue|1032|65
1003       |SERVICE3    |Wed|0859|4

该表包含大约 200 条记录，我的目标是将时间分组在一起，我已经通过这样做成功实现了:​​

select
case 
WHEN (Delivery_Time between '08:00:00' and '09:00:00') then '0800-0900'
WHEN (Delivery_Time between '09:00:00' and '10:00:00') then '0900-1000'
WHEN (Delivery_Time between '10:00:00' and '11:00:00') then '1000-1100'
WHEN (Delivery_Time between '11:00:00' and '12:00:00') then '1100-1200'
WHEN (Delivery_Time between '12:00:00' and '13:00:00') then '1200-1300'
WHEN (Delivery_Time between '13:00:00' and '14:00:00') then '1300-1400'
WHEN (Delivery_Time between '14:00:00' and '15:00:00') then '1400-1500'
WHEN (Delivery_Time between '15:00:00' and '16:00:00') then '1500-1600'
WHEN (Delivery_Time between '16:00:00' and '17:00:00') then '1600-1700'
WHEN (Delivery_Time between '17:00:00' and '18:00:00') then '1700-1800'
WHEN (Delivery_Time between '18:00:00' and '19:00:00') then '1800-1900'
WHEN (Delivery_Time between '19:00:00' and '20:00:00') then '1900-2000'
WHEN (Delivery_Time between '20:00:00' and '21:00:00') then '2000-2100'
      else 'Outside Opening Hours' 
  end as `Time Period`, 
   count(0) as 'count'
from service_data
group by `Time Period` 
order by count desc 
limit 20; 

这会产生以下结果:

TimePeriod  Count 
1700-1800   24
1500-1600   21
1200-1300   19
1400-1500   19
1800-1900   17
1100-1200   17
1300-1400   16
1600-1700   16
1000-1100   16
1900-2000   12
0800-0900   12
0900-1000   11

我现在要做的是将计数拆分，以便有 4 列标记为 SERVICE1、SERVICE2、SERVICE3 和 SERVICE4(Service_Type 列中的值。希望它看起来像这样:

TimePeriod|SERVICE1|SERVICE2|SERVICE3|SERVICE4
1700-1800 |   6    |   7    |    10  |   1
1500-1600 |   5    |   9    |    1   |   6
1200-1300 |   0    |   4    |    2   |   13`

这可能吗!？我确信一定是这样，但我一直在绞尽脑汁地试图解决这个问题，SQL 不是我的母语!任何帮助将不胜感激

我的第二个问题是:

我希望第二个查询能够执行上述所有操作，然后还将结果链接到 customer_data 表，该表的主键 customer_id 是 service_data 中的外键，并将 customer_id 链接到象限(customer_data 表中的列)值 NE、SE、SW、NW 取决于坐标)并按象限和服务对计数进行第二次分组，因此看起来像这样:

    TimePeriod| SERVICE1  | SERVICE2  | SERVICE3  | SERVICE4  |
   -----------|NE|SE|SW|NW|NE|SE|SW|NW|NE|SE|SW|NW|NE|SE|SW|NW|
    1700-1800 |2 |1 | 0| 3|4 | 0| 0|3 |2 |5 |2 |1 |0 |1 | 0| 0| 

这可能吗，还是我要求太多？我想知道是否可以以某种方式使用 SUM(IF) 函数来实现这一切？

Answer 1

这里有一些可以帮助您开始的东西，尽管我确实同意@Strawberry 的观点，即这需要一种编程语言来完成最后一步。

这并没有以任何方式针对性能或优雅进行优化，但我已经使用上面给出的数据对其进行了测试。

这是我的 CREATE TABLE 语句:

CREATE TABLE `service_data` (
  `services_id` int(11) NOT NULL,
  `service_type` varchar(45) DEFAULT NULL,
  `day` varchar(45) DEFAULT NULL,
  `time` time DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`services_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

首先获取您的时间范围。我已经把截止时间缩短了一秒，这样就不会重复计算正值的时间。

create view spans as
  select time(concat(hour(time),':00:00')) as fromtime, 
         time(concat(hour(addtime(time,'00:59:59')),':00:00')) as totime
  from service_data

现在查找每行数据的时间范围。

create view withspans as
  select * from service_data s1
  join spans s2
    on time between fromtime and totime

现在将数据汇总为数据透视表的输入。

create view summary as
  select fromtime, totime, service_type, count(*) as spancount
  from withspans
  group by fromtime, totime, service_type

现在通过派生表进行数据透视。

select w.fromtime, w.totime,
  s1.spancount as service1,
  s2.spancount as service2,
  s3.spancount as service3,
  s4.spancount as service4
  from summary w
  left join (select * from summary where service_type = 'SERVICE1') s1
    on s1.fromtime=w.fromtime and s1.totime=w.totime
  left join (select * from summary where service_type = 'SERVICE2') s2
    on s2.fromtime=w.fromtime and s2.totime=w.totime
  left join (select * from summary where service_type = 'SERVICE3') s3
    on s3.fromtime=w.fromtime and s3.totime=w.totime
  left join (select * from summary where service_type = 'SERVICE4') s4
    on s4.fromtime=w.fromtime and s4.totime=w.totime