sql - Postgres 计算数字或行并按时间戳对它们进行分组

Question

假设我在 postgres 中有一个只有 2 列的表:

• ID 是表的 PK (bigint)
• time 是 timestamp
的类型

有什么方法可以让 ID 按年份按时间分组 - 当时间为 2005 年 2 月 18 日时，它会适合 2005 组(所以结果是)

year  number of rows
1998  2
2005  5

AND 如果结果行数小于某个数字(例如 3)SQL 将返回结果按月

有点像

month            number of rows
(February 2018)  5
(March 2018)     2

这可能是 postgres SQL 中的一些好方法吗？

Answer 1

您可以使用窗口函数来完成(一如既往)。

我使用这张表:

TABLE times;

 id |               t               
----+-------------------------------
  1 | 2018-03-14 20:04:39.81298+01
  2 | 2018-03-14 20:04:42.92462+01
  3 | 2018-03-14 20:04:45.774615+01
  4 | 2018-03-14 20:04:48.877038+01
  5 | 2017-03-14 20:05:08.94096+01
  6 | 2017-03-14 20:05:16.123736+01
  7 | 2017-03-14 20:05:19.91982+01
  8 | 2017-01-14 20:05:32.249175+01
  9 | 2017-01-14 20:05:35.793645+01
 10 | 2017-01-14 20:05:39.991486+01
 11 | 2016-11-14 20:05:47.951472+01
 12 | 2016-11-14 20:05:52.941504+01
 13 | 2016-10-14 21:05:52.941504+02
(13 rows)

首先，按月分组(子查询per_month)。
然后使用窗口函数(子查询 with_year)添加每年的总和。
最后，使用 CASE 来决定您将输出哪一个并使用 DISTINCT 删除重复项。

SELECT DISTINCT
   CASE WHEN yc > 5
        THEN mc
        ELSE yc
   END AS count,
   CASE WHEN yc > 5
        THEN to_char(t, 'YYYY-MM')
        ELSE to_char(t, 'YYYY')
   END AS period
FROM (SELECT
         mc,
         sum(mc) OVER (PARTITION BY date_trunc('year', t)) AS yc,
         t
      FROM (SELECT
               count(*) AS mc,
               date_trunc('month', t) AS t
            FROM times
            GROUP BY date_trunc('month', t)
           ) per_month
     ) with_year
ORDER BY 2;

 count | period  
-------+---------
     3 | 2016
     3 | 2017-01
     3 | 2017-03
     4 | 2018
(4 rows)