主页 / dba / 问题 / 206055
Accepted
Vérace
Asked: 2018-05-08 07:37:25 +0800 CST

PostgreSQL - 不同月份的最大总和与多年的联系

  • 772

这个问题是 PostgreSQL 版本的一个关于 MySQL 5.6 here的问题。最初，这是两个 RDBMS 的一个问题，但有人向我建议，鉴于这两个系统的不同功能，我应该拆分这个问题——特别是我认为 CTE（WITH 子句）应该使查询更加优雅和可读！

假设我有一个肿瘤列表（这个数据是根据真实数据模拟的）：

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

您想找出给定月份中哪种特定肿瘤最常见 - 到目前为止一切顺利！

现在，您会注意到，对于 2017 年的第 1 个月，有一个平局 - 因此随机选择一个并给出它作为答案是没有任何意义的 - 所以必须包括平局 - 这使得问题更具挑战性。

我有一个解决方案，但它非常复杂 - 我想知道我的解决方案是否最优。PostgreSQL 小提琴就在这里！小提琴中的查询非常麻烦——我将看看使用 CTE 的情况。

我的第一个答案（适用于 PostgreSQL 和 MySQL）包含在小提琴中，但我不会在这里发布它，因为我相信它会被 PostgreSQL 的卓越功能所取代，它只是我对 MySQL 的答案的副本问题！

postgresql cte

3 个回答

  1. Best Answer

    对于给定的月份：

    SELECT tumour_count, illness
FROM (
   SELECT count(*) AS tumour_count, illness
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- given month: 2007-01
   AND    created_at  <  '2017-02-01'  -- optimized for index lookup
   GROUP  BY illness
   ) sub
WHERE  rnk = 1;

    上应该有一个索引(created_at)，或者甚至(created_at, illness)可能允许仅索引扫描。

    子查询比 Postgres 中的 CTE 快一点。因此，仅在需要时或性能不重要时才使用 CTE 。

    有关的：

    对于任何给定的时间段

    根据评论中的要求：

    SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, illness
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
ORDER  BY mon, illness;

    如果您有领先或悬挂的部分月份，请小心，计数可能会产生误导。

    这在功能上等同于ypercube 已经提供的功能。只是一些简化，可以更短/更快。并在给定时间段内添加过滤器。

    随着从表中读取的行所占比例的增加，索引支持变得不那么重要了——大约超过 5% 时索引支持就不再有用了。（例外情况适用，例如仅索引扫描。）

    通过聚合搭售同行，您每月仍然可以有1 行。喜欢：

    SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, string_agg(illness, ' | ')
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
GROUP  BY mon, tumour_count
ORDER  BY mon;

    db<>在这里摆弄

    • 5

  2. 使用窗口RANK()函数和DATE_TRUNC()（所以我们不使用EXTRACT()两次）：

      SELECT  
    EXTRACT(YEAR  FROM year_month) AS c_year,
    EXTRACT(MONTH FROM year_month) AS c_month,
    nature_of_illness,
    month_count
  FROM
    (
      SELECT 
        nature_of_illness,
        DATE_TRUNC('month', created_at) AS year_month,
        COUNT(*) AS month_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', created_at) 
                     ORDER BY COUNT(*) DESC)
          AS rnk
      FROM illness
      GROUP BY 
        DATE_TRUNC('month', created_at),
        nature_of_illness
    ) AS t
  WHERE rnk = 1 
  ORDER BY 
    year_month, nature_of_illness ;

    在dbfiddle.uk进行测试。

    • 4

  3. 我对此进行了尝试，并得到了与 ypercube 相同的总体想法。我认为他的更好，但我会提交这个，以防您觉得它有趣，并且因为我做起来很开心 :)

    SELECT
    C_YEAR,
    C_MONTH,
    NATURE_OF_ILLNESS,
    MONTH_COUNT
FROM(
SELECT
    EXTRACT(YEAR FROM CREATED_AT) C_YEAR,
    EXTRACT(MONTH FROM CREATED_AT) C_MONTH,
    NATURE_OF_ILLNESS,
    COUNT(NATURE_OF_ILLNESS) MONTH_COUNT,
    MAX(COUNT(NATURE_OF_ILLNESS)) OVER (
        PARTITION BY EXTRACT(YEAR FROM CREATED_AT),  
        EXTRACT(MONTH FROM CREATED_AT)) MAX_MONTH_COUNT
FROM 
    ILLNESS
GROUP BY 
    EXTRACT(YEAR FROM CREATED_AT),
    EXTRACT(MONTH FROM CREATED_AT),
    NATURE_OF_ILLNESS
) AS SICKNESS

WHERE MONTH_COUNT = MAX_MONTH_COUNT
ORDER BY 
    C_YEAR ASC, 
    C_MONTH ASC
    • 2

相关问题