sql - postgres 的统计模式

Question

我有一个具有此架构的表:

create table mytable (creation_date timestamp,
                      value int,
                      category int);

我希望每个类别每小时最多出现一个值，仅在工作日。我取得了一些进展，我现在有这样的查询:

select category,foo.h as h,value, count(value) from mytable, (
      select date_trunc('hour', 
                        '2000-01-01 00:00:00'::timestamp+generate_series(0,23)*'1 hour'::interval)::time as h) AS foo 
      where date_part('hour',creation_date) = date_part('hour',foo.h) and
            date_part('dow',creation_date) > 0 and date_part('dow',creation_date) < 6
      group by category,h,value;

结果我得到了这样的东西:

category |    h     |  value  | count 
---------+----------+---------+-------
       1 | 00:00:00 |       2 |     1
       1 | 01:00:00 |       2 |     1
       1 | 02:00:00 |       2 |     6
       1 | 03:00:00 |       2 |    31
       1 | 03:00:00 |       3 |    11
       1 | 04:00:00 |       2 |    21
       1 | 04:00:00 |       3 |     9
       1 | 13:00:00 |       1 |    14
       1 | 14:00:00 |       1 |    10
       1 | 14:00:00 |       2 |     7
       1 | 15:00:00 |       1 |    52

例如在 04:00 我必须取值 2 和 3，计数分别为 21 和 9，我只需要计数最高的值，这将是统计模式。

顺便说一句，我有超过 200 万条记录

Answer 1

这可以更简单:

SELECT DISTINCT ON (category, extract(hour FROM creation_date)::int)
       category
     , extract(hour FROM creation_date)::int AS h
     , count(*)::int AS max_ct
     , value
FROM   mytable
WHERE  extract(isodow FROM creation_date) < 6 -- no sat or sun
GROUP  BY 1,2,4
ORDER  BY 1,2,3 DESC;

基本上这些是步骤:

1. 排除周末(WHERE ...)。使用 ISODOW来简化表达式。
2. 从 timestamp 中提取 hour 作为 h。
3. 按类别、h 和值 分组。
4. 计算三者组合的行数；转换为 integer - 我们不需要 bigint。
5. 按类别、h 和最高计数 (DESC) 排序。
6. 只选择每个 (category, h) 对应的 category 的第一行(最高计数)。

我能够在一个查询级别执行此操作，因为 DISTINCT 在聚合函数之后 应用。

对于任何 (category, h)，结果将保留 no 行，根本没有任何条目。如果您需要填写空白，LEFT JOIN 到此:

SELECT c.category, h.h
FROM   cat_tbl c
CROSS  JOIN (SELECT generate_series(0, 23) AS h) h