主页 / dba / 问题 / 281308
Accepted
Nuala Carvill
Asked: 2020-12-11 15:18:47 +0800 CST

将数据汇总为每小时计数，每天平均

  • 772

我需要总结一个数据集以显示每天每小时的总数。该数据集包含为期 4 周的旅行卡交易。我想查看 28 天中每一天的每小时总交易量（00:00-01:00、01:00-02:00、...23:00-00:00）。

如果这是可以实现的，我希望重新采样以分别显示工作日和周末每小时的平均交易量。

日期时间字段是没有时区的数据类型时间戳，即“YYYY-MM-DD HH:MM:SS”。

这是 PostgreSQL 可以完成的事情吗？

postgresql aggregate

1 个回答

  1. Best Answer

    date_tunc()是关键。

    每小时交易

    SELECT date_trunc('hour', datetime) AS hour
     , count(*) AS transactons
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

    每小时平均交易量

    ...分别适用于工作日和周末。

    SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(transactions), 2) AS avg_transactions
FROM  (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub
GROUP  BY 1;

    0.. 工作日
    1.. 周末

    ISODOW为 Mon - Sun 生成 1 - 7。周六和周日是“周末”。整数除法x/6返回0工作日和1周末。

    包括没有活动的时间

    上述查询仅观察实际交易的时间。要包括没有事务的小时数（表中没有行），事务值为 0，（外部）在给定的 28 天期间内加入每小时栅格/网格。并使用COALESCE(). 喜欢：

    SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(COALESCE(transactions, 0)), 2) AS avg_transactions
FROM  (
   SELECT generate_series(timestamp '2020-12-01' -- first day
                        , timestamp '2020-12-28' -- last day
                          + interval '1 day - 1 hour'
                        , interval '1 hour')
   ) grid(hour)
LEFT JOIN (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub USING (hour)
GROUP  BY 1;

    该表达式timestamp '2020-12-28' + interval '1 day - 1 hour'生成时间序列的最新关注小时。

    db<>在这里摆弄

    进一步阅读：

    • 2

相关问题