主页 / user-226442

James's questions

Martin Hope
James
Asked: 2021-03-23 21:21:07 +0800 CST
  • 0

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

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