我想为所有加入并执行2个核心操作(添加图片或查看图片(中至少1个的用户构建一个D0-D30，N天的保留期图表。
So for example, user 1 and 2 both join on 2022-10-01 (day 0)
-----------------------------------------------------------------------------------------
* user 1 views a picture (day 0) | * user 2 adds a picture (day 0)
* user 1 adds a picture (day 1) | * user 2 add a picture (day 1)
* user 1 does nothing (day 2) | * user 2 views a picture (day 2)
* user 1 views a picture (day 3) | * user 2 adds and views multiple pictures (day 3)
* user 1 does nothing (day 4) | * user 2 does nothing (day 4)
保留期图表如下所示：
date_joined | # of users | D0 | D1 | D2 | D3 | D4 | ... | D30
----------------------------------------------------------------------
2022-10-01 | 2 | 100% | 100% | 50% | 100% | 0% | ... | X%
- D2为50%，因为用户1什么都没做，而用户2查看了一张图片(2个人中有1个人执行了核心操作(
- D4为0%，因为两个用户都没有做任何操作
需要以下表格：
用户表
-----------------
id | join_date
-----------------
1 | 2022-10-01
2 | 2022-10-01
图片表
id | timestamp | userId | url
-------------------------------------------------
1 | 2022-10-01 10:00:00 | 2 | http://www...
2 | 2022-10-02 08:00:00 | 1 | http://www...
3 | 2022-10-02 09:00:00 | 2 | http://www...
4 | 2022-10-04 05:00:00 | 2 | http://www...
5 | 2022-10-04 06:00:00 | 2 | http://www...
查看表
id | timestamp | userId
----------------------------------
1 | 2022-10-01 11:00:00 | 1
2 | 2022-10-03 07:00:00 | 2
3 | 2022-10-04 09:00:00 | 1
4 | 2022-10-04 10:00:00 | 2
5 | 2022-10-04 11:00:00 | 2
到目前为止，我已经尝试过：
- 我可以每天对那些执行了核心操作但不知道如何在30天内跟踪他们的用户进行分类，并对用户加入的每一天进行跟踪
我所拥有的：
date | # users who performed core action
-----------------------------------------
2022-10-01 | 100
2022-10-02 | 59
2022-10-03 | 129
2022-10-04 | 143
这与我所需要的不接近的原因是，每天我都会得到执行核心操作的用户总数，而不仅仅是在X日期加入的用户执行的操作。
I.e:2022-10-01向我展示了平台中至少执行了2个核心动作中的1个的所有用户。
我需要跟踪在2022-10-01加入30天的用户，在2022-10-02加入30天等的用户。
如有任何帮助，我们将不胜感激！
您的请求的解决方案并不明显。
从你的保留期图表的外观开始，它有30列，标签来自"；D1〃；至"；D30"；，我们将创建一个具有相应标签的专用复合类型；d"；小写而不是"；D"；大写，因为postgres自动将列标签转换为小写：
CREATE OR REPLACE PROCEDURE composite_type() LANGUAGE plpgsql AS $$
DECLARE
txt text ;
BEGIN
SELECT string_agg('d' || i || ' text',',') INTO txt
FROM generate_series(1,30) AS i ;
DROP TYPE IF EXISTS composite_type ;
EXECUTE 'CREATE TYPE composite_type AS (' || txt || ')' ;
END ; $$ ;
CALL composite_type() ;
然后我们有一个相当复杂的查询来完成这项工作：
WITH list AS (
SELECT u.join_date AS date_joined
, 'd' || d.id AS date_label
, array_length(array_agg(DISTINCT u.id), 1) AS "# of users"
, to_char(COALESCE(array_length(array_remove(array_agg(DISTINCT COALESCE(p.userId, v.userId)), NULL), 1)
/ array_length(array_agg(DISTINCT u.id), 1) :: numeric * 100, 0), '999') || '%' AS ratio
FROM "user" AS u
CROSS JOIN LATERAL generate_series (u.join_date, u.join_date + interval '30 days', interval '1 day') WITH ORDINALITY AS d(day, id)
LEFT JOIN "picture" AS p ON p.userId = u.id AND p.timestamp :: date = d.day
LEFT JOIN "view" AS v ON v.userId = u.id AND v.timestamp :: date = d.day
GROUP BY u.join_date, d.id
)
SELECT date_joined, "# of users"
, (jsonb_populate_record(NULL :: composite_type, jsonb_object_agg(date_label, ratio))).*
FROM list
GROUP BY date_joined, "# of users"
在查询的CTE部分：
- 查询从表
user开始，按date_joined对用户进行分组 - 然后它与
date_joined之后的30天交叉连接，其列表由函数generate_series()自动生成 - 然后它离开了与
picture和view表的联接。左侧联接允许标识用户组中没有用户执行操作的日期 - 然后，它使用DISTINCT子句将每个date_joind和每个后续日期的用户聚合到一个数组中，这样就不会对在同一日期执行多个操作的同一用户进行双重计数
- 然后，它从数组中删除NULL值，并计算与该日期的活动用户数相对应的数组长度
- 此活动用户数除以按
date_joined分组的用户总数，并格式化为百分比
查询的最后部分从CTE检索所有行，并通过使用CCD_ 8和CCD_；d1〃"d30"；以及相应的值。
结果是：
| date_joined | 的#用户 | d1 | <2th>d2>d3 | d4 | <1th>d5<5th>d6d7 | d18 | <1th>d19d10 | >th>d11>d17d18 | >th>d19>th>d20d21 | <1th>d22<2th>d23<5th>d24d25>26d27 | d28 | <29><1th>d30
|---|---|---|---|---|---|---|---|---|---|---|---|
| 2022-10-01 | 2 | 100% | <100%>0% | 0%>0% | 0% | 0% | 0%td>