sql - 从一张表中计算多个时间戳的总计

Question

我有一个表，它有大约 5000 条记录，有 4 种类型的订单在多个时间戳，我一直在尝试计算多个时间戳的订单数，比如 00 到 04hrs，04hrs 到 24hrs。我只能获得 1 个时间戳中的任何一个的结果。感谢您的帮助:)

SELECT
    DECODE(order_type,1,'Multi Sim',2,'Single Sim',3,'Sim Replacement',7,'Data SIM with Router') AS order_type,
    COUNT(order_type) AS total0hrs_4hrs,
    COUNT(order_type) AS total4hrs_24hrs,
    COUNT(order_type) AS grand_total
FROM
    sids
WHERE
    order_time >= to_timestamp('2020-09-01 00:00:00.0000','YYYY-MM-DD HH24:MI:SS.FF4')
    AND order_time <= to_timestamp('2020-09-01 03:59:59.9999','YYYY-MM-DD HH24:MI:SS.FF4')
    AND order_type IN (
        1,
        2,
        3,
        7
    )
GROUP BY
    order_type
HAVING
    COUNT(order_type) > 0
ORDER BY
    1;

标签： sqloracle

Answer 1

CASE在聚合函数中使用表达式：

SELECT DECODE(
         order_type,
         1, 'Multi Sim',
         2, 'Single Sim',
         3, 'Sim Replacement',
         7, 'Data SIM with Router'
       ) AS order_type,
       COUNT(
         CASE
         WHEN order_time - TRUNC( order_time ) < INTERVAL '4' HOUR
         THEN 1
         ELSE NULL
         END
       ) AS total0hrs_4hrs,
       COUNT(
         CASE
         WHEN order_time - TRUNC( order_time ) < INTERVAL '4' HOUR
         THEN NULL
         ELSE 1
         END
       ) AS total4hrs_24hrs,
       COUNT(order_type) AS grand_total
FROM   sids
WHERE  order_time >= TIMESTAMP '2020-09-01 00:00:00.0000'
AND    order_time <  TIMESTAMP '2020-09-02 00:00:00.0000'
AND    order_type IN ( 1, 2, 3, 7 )
GROUP BY
       order_type
ORDER BY
       1;