SQL循环遍历连续时间序列数据的有效方法，无需重复计数即可识别唯一用户

Question

我正在做一个项目，在这个项目中，我有一些用户在从Day1到Day8的一段时间内“注册”了。然而，由于问题的情况，用户可以多次“注册”。这使得相同的用户能够在Dayx和Dayz中注册。注意:我使用的是最新稳定版本的PostGreSQL

我们的目标是只计算每天的唯一注册数量，而不重复计算任何用户。这意味着Day8中的注册总数也需要考虑到第1-7天的注册。

我目前拥有的解决方案在技术上是可行的，但它非常笨拙，需要花费很长时间来查询，而且伸缩性不好。理想情况下，SQL查询需要针对时间x和时间y之间的任何时间段进行缩放，而不必为每个单独的时间段手动编写代码块。

正如你在下面的代码中看到的，从技术上讲，它给了我写的答案，但它很麻烦，很慢，而且不能扩展。寻求帮助寻找一个优雅的、可伸缩的、不需要30分钟运行的解决方案。

注意:我可以用Python写得更优雅，但不确定Python对存储在RDBMS中的大型数据集有多好的伸缩性(例如:使用SQL提取所有原始数据，然后将CSV导入到python中，在python中，python脚本将进行计算，而不是使用SQL)

TABLE DATA:
+-----------+--------------+-----------------------------------------------+
| cookie_id | time_created |                      URL                      |
+-----------+--------------+-----------------------------------------------+
| 3422erq   | 2018-10-1    | https:data.join/4wr08w40rwj/utm_source.com    |
| 3421ra    | 2018-10-1    | https:data.join/convert/45824234/utm_code.com |
| 321af     | 2018-10-2    | https:data.join/utm_source=34342.com          |
+-----------+--------------+-----------------------------------------------+

SELECT COUNT(DISTINCT cookie_id), time_created FROM Data WHERE url LIKE ('%join%') 
AND time_created IN (SELECT MIN(time_created) FROM Data)
GROUP BY time_created

--获取Day1中所有唯一用户的代码(5,304个唯一用户)

SELECT COUNT(DISTINCT cookie_id), time_created FROM Data WHERE url LIKE ('%join%') 
AND time_created IN (SELECT MIN(time_created +1) FROM Data)
AND cookie_id NOT IN (SELECT DISTINCT cookie_id FROM Data WHERE time_created = '2018-10-01')
GROUP BY time_created

--获取Day2中所有唯一用户的代码(9,218个唯一用户)

SELECT COUNT(DISTINCT cookie_id), time_created FROM Data WHERE url LIKE ('%join%') 
AND time_created IN (SELECT MIN(time_created +2) FROM Data)
AND cookie_id NOT IN (SELECT DISTINCT cookie_id FROM Data WHERE time_created BETWEEN '2018-10-01' AND '2018-10-02')
GROUP BY time_created

--获取Day3中所有唯一用户的代码(8,745个唯一用户)

预期和实际结果是相同的。然而，代码没有伸缩性，而且速度非常慢。

Answer 1

因此，给出这个表：

CREATE TABLE data
(
    cookie_id text,
    time_created date,
    url text
)

(是，没有索引)

我在一个随机(2018-10-01::date + (10*random())::int)日期生成了550万行，长度为5个0-9A-F个字符的随机cookie_ids，其中每100行都有https:data.join/.... url，而其他行都是垃圾。

您的第二个查询大约花了8.5分钟。另一方面，这个花了大约0.2秒：

with count_per_day as
(
    select time_created, count(*) as unique_users from (
        select cookie_id
             , time_created
             , row_number() over (partition by cookie_id order by time_created) occurrence
          from data
         where url like 'https:data.join%'
           and time_created between '2018-10-01' and '2018-10-08'
    ) oc
    where occurrence = 1
    group by time_created
)
select time_created, unique_users, sum(unique_users) over (order by time_created) as running_sum
  from count_per_day

同样，没有索引。如果你有更大数量级的计数，那么(left(url, 15), time_created, cookie_id)上的索引和将url条件更改为left(url, 15) = 'https:data.join'会将其降至50ms以下。