sql - Google Bigquery SQL - 按日期获取最新的唯一值

Question

#EDIT - 根据评论，我重新表述我的问题

我有一个 BigQuery 表，我想用它来获取我的应用程序的一些 KPI。在此表中，我将每个创建或更新保存为一个新行，以便保留更好的历史记录。所以我有好几次具有不同状态的相同数据。

表格示例:

uuid  |status     |date         
––––––|–––––––––––|––––––––––      
3     |'inactive' |2018-05-12
1     |'active'   |2018-05-10
1     |'inactive' |2018-05-08
2     |'active'   |2018-05-08
3     |'active'   |2018-05-04
2     |'inactive' |2018-04-22
3     |'inactive' |2018-04-18

我们可以看到每个数据都有多个值。

我想得到什么:

我想要当前“事件”条目的数量(因此之后必须没有具有相同 uuid 的“非事件”条目)。更复杂的是，我需要这个每天的总数。因此，对于每一天，“活跃”条目的数量，包括前几天的条目。

所以对于这个例子，我应该得到这样的结果:

date        | actives
____________|_________
2018-05-02  |   0
2018-05-03  |   0
2018-05-04  |   1
2018-05-05  |   1
2018-05-06  |   1
2018-05-07  |   1
2018-05-08  |   2
2018-05-09  |   2
2018-05-10  |   3
2018-05-11  |   3
2018-05-12  |   2

实际上，我已经设法获得了一天的大量 active 物质。 但我的问题是当我想要每天的结果时。

我尝试过的:

我遇到了两个解决方案，每个解决方案都返回不同的错误。

第一个解决方案:

WITH
  dates AS(
      SELECT GENERATE_DATE_ARRAY(
          DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH), CURRENT_DATE(), INTERVAL 1 DAY)               
      arr_dates )
SELECT
  i_date date,
  (
  SELECT COUNT(uuid)
  FROM (
    SELECT
      uuid, status, date,
      RANK() OVER(PARTITION BY uuid ORDER BY date DESC) rank
    FROM users
    WHERE
      PARSE_DATE("%Y-%m-%d", FORMAT_DATETIME("%Y-%m-%d",date)) <= i_date
  )
  WHERE
    status = 'active'
    and rank = 1
    ## rank is the condition which causes the error
  ) users
FROM
  dates, UNNEST(arr_dates) i_date
ORDER BY i_date;

带有 RANK() OVER 的 SELECT 正确返回具有排名列的用户，这让我知道每个 uuid 的最后一个条目。但是当我尝试这个时，我得到了一个:不支持引用其他表的相关子查询，除非它们可以取消关联，例如通过将它们转换为有效的 JOIN。因为 rank = 1 条件。

第二种解决方案:

WITH
  dates AS(
      SELECT GENERATE_DATE_ARRAY(
          DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH), CURRENT_DATE(), INTERVAL 1 DAY)               
      arr_dates )
SELECT
  i_date date,
  (
  SELECT
    COUNT(t1.uuid)
  FROM
    users t1
  WHERE
    t1.date = (
      SELECT MAX(t2.date)
      FROM users t2
      WHERE
        t2.uuid = t1.uuid
        ## Here that's the i_date condition which causes problem 
        AND PARSE_DATE("%Y-%m-%d", FORMAT_DATETIME("%Y-%m-%d", t2.date)) <= i_date 
    )
    AND status='active' ) users
FROM
  dates,
  UNNEST(arr_dates) i_date
ORDER BY i_date;

在这里，第二个选择也有效，并正确返回当天的活跃用户数。但问题是当我尝试使用 i_date 来检索多天的数据时。在这里我得到了一个 LEFT OUTER JOIN cannot be used without a condition that is equality of fields from both sides of join. 错误...

哪种方案更能成功？我应该改变什么？

而且，如果我存储数据的方式不好，我应该如何进行才能保持准确的历史记录？

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT date, COUNT(DISTINCT uuid) total_active 
FROM `project.dataset.table`
WHERE status = 'active'
GROUP BY date 
-- ORDER BY date   

Update to address your "rephrased" question :o)
Below example is using dummy data from your question

#standardSQL
WITH `project.dataset.users` AS (
  SELECT 3 uuid, 'inactive' status, DATE '2018-05-12' date UNION ALL
  SELECT 1, 'active', '2018-05-10' UNION ALL
  SELECT 1, 'inactive', '2018-05-08' UNION ALL
  SELECT 2, 'active', '2018-05-08' UNION ALL
  SELECT 3, 'active', '2018-05-04' UNION ALL
  SELECT 2, 'inactive', '2018-04-22' UNION ALL
  SELECT 3, 'inactive', '2018-04-18' 
), dates AS (
  SELECT day FROM UNNEST((
    SELECT GENERATE_DATE_ARRAY(MIN(date), MAX(date))
    FROM `project.dataset.users`
  )) day
), active_users AS (
  SELECT uuid, status, date first, DATE_SUB(next_status.date, INTERVAL 1 DAY) last FROM (
    SELECT uuid, date, status, LEAD(STRUCT(status, date)) OVER(PARTITION BY uuid ORDER BY date ) next_status
    FROM `project.dataset.users` u
  )
  WHERE status = 'active'
)
SELECT day, COUNT(DISTINCT uuid) actives
FROM dates d JOIN active_users u
ON day BETWEEN first AND IFNULL(last, day)
GROUP BY day 
-- ORDER BY day

结果

Row day         actives  
1   2018-05-04  1    
2   2018-05-05  1    
3   2018-05-06  1    
4   2018-05-07  1    
5   2018-05-08  2    
6   2018-05-09  2    
7   2018-05-10  3    
8   2018-05-11  3    
9   2018-05-12  2