时间戳

首页 > 解决方案 > 获取第一列空白postgres

sql - 获取第一列空白postgres

问题描述

SELECT CASE WHEN date_part('hour',created_at) BETWEEN 3 AND 15 THEN '9am-3pm' 
            WHEN date_part('hour',created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END "time window",COUNT(*) FROM tickets where created_at < now()
GROUP BY  CASE WHEN date_part('hour',created_at) BETWEEN 3 AND 15 THEN '9am-3pm' WHEN date_part('hour',created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END;

 time window | count 
-------------+-------
             |     6
 9am-3pm     |    69

是否可以按日期和时间过滤它，以便我的结果集看起来像

Date        | time window | count 
------------+-------------+-------
12-01-2020  |      9am-3pm|     6
12-01-2020  |      3pm-6pm|    69
13-01-2020  |      9am-3pm|    12
13-01-2020  |      3pm-6pm|    14

标签： sqlpostgresql

解决方案

我们可以使用日历表方法来处理这个要求：

WITH dates AS (
    SELECT '12-01-2020' AS created_at UNION ALL
    SELECT '13-01-2020'
),
tw AS (
    SELECT '9am-3pm' AS "time window" UNION ALL
    SELECT '3pm-6pm'
),
cte AS (
    SELECT
        created_at::date AS created_at,
        CASE WHEN DATE_PART('hour', created_at) BETWEEN 3 AND 15 THEN '9am-3pm'
             WHEN DATE_PART('hour', created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END "time window",
        COUNT(*) AS cnt
    FROM tickets
    WHERE created_at < NOW()
    GROUP BY 1, 2
)

SELECT
    d.created_at,
    tw."time window",
    COALESCE(t.cnt, 0) AS count
FROM dates d
CROSS JOIN tw
LEFT JOIN cte t
    ON d.created_at = t.created_at AND tw."time window" = t."time window"
ORDER BY
    d.dt,
    tw."time window";

推荐阅读