24小时内不同状态的SQL总和按小时分组

Question

我试图按小时总结 24 小时组内的状态。我有一个订单、订单状态和状态表。

订购表:

+---------+-------------------------+
| orderid | orderdate               |
+---------+-------------------------+
| 1       | 2015-09-16 00:04:19.100 |
| 2       | 2015-09-16 00:01:19.490 |
| 3       | 2015-09-16 00:02:33.733 |
| 4       | 2015-09-16 00:03:58.800 |
| 5       | 2015-09-16 00:01:16.020 |
| 6       | 2015-09-16 00:01:16.677 |
| 7       | 2015-09-16 00:02:06.920 |
+---------+-------------------------+

订单状态表:

+---------+----------+
| orderid | statusid |
+---------+----------+
| 1       | 11       | 
| 2       | 22       |
| 3       | 22       |
| 4       | 11       |
| 5       | 22       |
| 6       | 33       |
| 7       | 11       |
+---------+----------+

状态表:

+----------+----------+
| statusid | status   |
+----------+----------+
| 11       | PVC      |
| 22       | CCC      |
| 33       | WWW      |
|          |          |
+----------+----------+

我正在尝试编写 SQL 来显示 24 小时内按小时分组的不同 orderid 的状态计数，如下所示:

+------+-----+-----+-----+
| Hour | PVC | CCC | WWW |
+------+-----+-----+-----+
| 1    | 0   | 2   | 1   |
| 2    | 1   | 1   | 0   |
| 3    | 1   | 0   | 0   |
| 4    | 1   | 0   | 0   |
+------+-----+-----+-----+

到目前为止，这是我的 SQL。我试图获得每个订单状态的总和:

SELECT
    DATEPART(hour, o.orderdate) AS Hour,
    SUM( 
        CASE (
            SELECT stat.status 
            FROM Status stat, orderstatus os  
            WHERE stat.status IN ('PVC') AND os.orderid = o.id AND os.statusid = stat.id
        ) 
            WHEN 'PVC' THEN 1 
            ELSE 0 
        END 
    ) AS PVC,
    SUM(
        CASE (
            SELECT stat.status 
            FROM Status stat, orderstatus os 
            WHERE stat.status IN ('WWW') AND os.orderid = o.id AND os.statusid = stat.id
        ) 
          WHEN 'CCC' THEN 1 
          ELSE 0 
        END 
    ) AS CCC,
    SUM( 
        CASE (
            SELECT stat.status 
            FROM Status stat, orderstatus os 
            WHERE stat.status IN ('CCC') AND os.orderid = o.id AND os.statusid = stat.id) 
            WHEN 'WWW' THEN 1 
            ELSE 0 
        END 
    ) AS WWW 
FROM orders o 
WHERE o.orderdate BETWEEN DATEADD(d,-1,CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP 
GROUP BY DATEPART(hour, o.orderdate) 
ORDER BY DATEPART(hour, o.orderdate);

Answer 1

给你 - 我忽略了你的数据中的错误，因为如果状态表真的像你的示例数据中那样有重复的 id，这将失败。

SELECT hour, sum(PVC) as PVC, sum(CCC) as CCC, sum(WWW) as WWW
from (
  select datepart(hour,orderdate) as hour, 
        case when s.status = 'PVC' then 1 else 0 end as PVC,
        case when s.status = 'CCC' then 1 else 0 end as CCC,
        case when s.status = 'WWW' then 1 else 0 end as WWW
  from order o 
  join orderstatus os on o.orderid = os.orderid
  join status s on s.statusid = os.statusid
) sub
group by hour