在滚动时间范围内统计唯一ID [英] Count unique ids in a rolling time frame

问题描述

我有一个简单的表格，里面有许多ID和日期。

  ID日期
 10R46 2014-11-23 
 10R46 2016-04-11 
 100R9 2016-12-21 
 10R91 2013-05-03 
 ... ... 
  

我想制定一个查询计算日期的滚动时间段的唯一ID，例如十天。这意味着对于每个日期，它应该给我在该日期和10天之前的唯一ID的数目。结果应该看起来像这样。

  UniqueTenDays日期
 200 2014-11-23 
 324 2014-11-24 
 522 2014-11-25 
 532 2014-11-26 
 ... ... 
  

沿着波纹管的某处，但我意识到我需要应用WHERE子句并以某种方式计算每个Date的ID。

  SELECT日期，COUNT（DISTINCT ID）
 FROM T 
 WHERE日期DAY_SUB（日期，间隔10天）和日期
 GROUP BY日期
 ORDER BY日期
  

预先致谢。

解决方案

以下是适用于BigQuery标准SQL的内容：


$ （
SELECT dt，STRING_AGG（DISTINCT id）AS用户
FROM`
$' project.dataset.yourtable`
GROUP BY dt
），temp2 AS（
SELECT
dt，
STRING_AGG（users）OVER（ORDER BY UNIX_DATE（dt）RANGE用户
从temp1
）
SELECT dt，
（SELECT COUNT（DISTINCT id）FROM UNNEST（SPLIT（users））AS id）UniqueTenDays
从temp2

您可以使用下面的虚拟数据测试/播放它

（

）

#standardSQL
WITH`project.dataset.yourtable` AS（
SELECT'10R46 'id，DATE'2014-11-23'dt UNION ALL
SELECT'10R46'，DATE'2016-04-11'UNION ALL
SELECT'10R46'，DATE'2016-04-12' UNION ALL
SELECT'10R47'，DATE'2016-04-13'UNION ALL
SELECT'10R48'，DATE'2016-04-14'UNION ALL
SELECT'100R9'，DATE '2016-12-21'UNION ALL
SELECT'10R91'，DATE'2013-05-03'
），temp1 AS（
SELECT dt，STRING_AGG（DISTINCT id）AS users
FROM`project.dataset.yourtable`
GROUP BY dt
），temp2 AS（
SELECT
dt，
STRING_AGG（users）OVER（ORDER BY UNIX_DATE（dt）RANGE BETWEEN 10 PRECEDING AND CURRENT ROW）用户
FROM temp1
）
SELECT dt，
（SELECT COUNT（DISTINCT id）FROM UNNEST（SPLIT（users）） AS id）UniqueTenDays
FROM temp2


I have a simple table as bellow with lots of IDs and dates.

ID      Date
10R46   2014-11-23  
10R46   2016-04-11  
100R9   2016-12-21
10R91   2013-05-03 
...     ...

I want to formulate a query which counts the unique IDs for a rolling time frame of dates, for example ten days. Meaning that for each date it should give me the number of unique IDs between that date and 10 days back. Result should look something like this.

UniqueTenDays    Date
200              2014-11-23 
324              2014-11-24 
522              2014-11-25
532              2014-11-26 
...              ...

Something along the lines of the bellow but I realise I need to apply the WHERE clause and count the IDs for each Date somehow.

SELECT Date, COUNT(DISTINCT ID)
FROM T 
WHERE Date BETWEEN DATE_SUB(Date, INTERVAL 10 DAY) AND Date
GROUP BY Date
ORDER BY Date

Thanks in advance.

解决方案

Below is for BigQuery Standard SQL

#standardSQL
WITH temp1 AS (
  SELECT dt, STRING_AGG(DISTINCT id) AS users
  FROM `project.dataset.yourtable`
  GROUP BY dt
), temp2 AS (
  SELECT
    dt, 
    STRING_AGG(users) OVER(ORDER BY UNIX_DATE(dt) RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) users
  FROM temp1
)
SELECT dt, 
  (SELECT COUNT(DISTINCT id) FROM UNNEST(SPLIT(users)) AS id) UniqueTenDays
FROM temp2

you can test / play with it using dummy data as below

#standardSQL
WITH `project.dataset.yourtable` AS (
  SELECT '10R46' id,  DATE '2014-11-23' dt UNION ALL  
  SELECT '10R46',     DATE '2016-04-11' UNION ALL  
  SELECT '10R46',     DATE '2016-04-12' UNION ALL  
  SELECT '10R47',     DATE '2016-04-13' UNION ALL  
  SELECT '10R48',     DATE '2016-04-14' UNION ALL  
  SELECT '100R9',     DATE '2016-12-21' UNION ALL
  SELECT '10R91',     DATE '2013-05-03'
), temp1 AS (
  SELECT dt, STRING_AGG(DISTINCT id) AS users
  FROM `project.dataset.yourtable`
  GROUP BY dt
), temp2 AS (
  SELECT
    dt, 
    STRING_AGG(users) OVER(ORDER BY UNIX_DATE(dt) RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) users
  FROM temp1
)
SELECT dt,  
  (SELECT COUNT(DISTINCT id) FROM UNNEST(SPLIT(users)) AS id) UniqueTenDays
FROM temp2

这篇关于在滚动时间范围内统计唯一ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！