主页 / dba / 问题 / 287519
Accepted
James
Asked: 2021-03-23 21:21:07 +0800 CST

基于开始/结束日期名册的活跃用户月度摘要

  • 772

我有一张表格，说明谁参与了一个项目，从开始日期到结束日期。我需要一些帮助来编写一个查询，该查询将自今年年初以来在每个月底返回“活跃”用户的数量。

DROP TABLE "public"."roster";
CREATE TABLE "public"."roster" ("id" int,"user_id" int,"project_id" int,"start_date" datetime,"end_date" datetime,"closed_date" datetime, PRIMARY KEY ("id"));

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(1, 1, 1, '2019-05-27 00:00:00', '2021-01-15 00:00:00', NULL);

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(2, 2, 2, '2020-05-27 00:00:00', '2021-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(3, 3, 3, '2020-05-27 00:00:00', '2024-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(4, 4, 4, '2020-05-27 00:00:00', '2021-03-05 00:00:00', NULL);

id  user_id project_id  start_date            end_date              closed_date
1   1       1           2019-05-27 00:00:00   2021-01-15 00:00:00   NULL
2   2       2           2020-05-27 00:00:00   2021-02-01 00:00:00   2020-02-05 00:00:00
3   3       3           2020-05-27 00:00:00   2024-02-01 00:00:00   2020-02-05 00:00:00
4   4       4           2020-05-27 00:00:00   2021-03-05 00:00:00   NULL

结果将显示每个月有多少不同的用户有一个活跃的项目（仅自今年年初以来）。

所以对于上面的数据集，我们可以看到所有 4 个项目在 2021 年 1 月月份都是“活跃的”，因为结束日期在未来。

2021-01-31 | 3 (4 projects were active in some way, during January)
2021-02-28 | 2 (3 projects were active in some way, during February)
2021-03-31 | 1 (1 project was active in some way, during March)

最后一点复杂性是，有时项目可以在 EndDate 之前关闭，我想排除任何 endDate 在未来但项目实际上已经关闭的用户。

例如，在上面的数据集中，第三个项目的结束日期为 2024 年 2 月，但该项目于 2021 年 2 月结束。所以从技术上讲，这个人在 2021 年 1 月和 2 月是活跃的，但不是 2021 年 3 月及以后。

ps 希望得到 Redshift 的答案

redshift

1 个回答

  1. Best Answer

    ps 希望在 Postgres 中得到答案（我正在使用 Redshift）

    PostgreSQL 的解决方案：

    WITH cte AS ( SELECT '2021-01-01'::DATE AS month_start, '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-01', '2021-02-28' UNION ALL
              SELECT '2021-03-01', '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date <= month_end 
  AND LEAST(end_date, closed_date) >= month_start
GROUP BY cte.month_end
ORDER BY cte.month_end;

    或者

    WITH cte AS ( SELECT '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-28' UNION ALL
              SELECT '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date <= month_end 
  AND LEAST(end_date, closed_date) >= DATE_TRUNC('month', month_end)
GROUP BY cte.month_end
ORDER BY cte.month_end;

    https://dbfiddle.uk/?rdbms=postgres_12&fiddle=710260e9fa44a89cc9c2d536739f7c92

    用您需要的代码替换cte代码（在第一个变体中为感兴趣的月份生成月份的第一天和最后几天，或者在第二个变体中仅生成最后几天的月份）。例如，使用日期生成器，从YYYY-MM-01下个月开始表示感兴趣的月份，步骤为一个月，然后减去 - 一个月的第一天和最后一天的一天。

    • 2

相关问题