Longest Consecutive Days Count for BigQuery

问题: Right now I just have an aggregate of how many days a user has worked. I'm trying to change this query to most continuous days worked. Where u12345 would be 4 and u1 woul...

问题:

Right now I just have an aggregate of how many days a user has worked. I'm trying to change this query to most continuous days worked.

Where u12345 would be 4 and u1 would be 2.

Is this possible to do with a BigQuery statement?

EDIT I am Kind of close with the following query but my u1 is getting 3 instead of 2.

SELECT MIN(e.timestamp) as date_created, e.uid, COUNT(e.uid) + 1 AS streak
FROM OnSite e
LEFT JOIN OnSite ee 
  ON e.uid = ee.uid 
AND DATE(e.timestamp) = DATE(DATE_ADD(ee.timestamp, INTERVAL -1 DAY))
WHERE ee.uid IS NOT NULL
GROUP BY e.uid;

Schema (MySQL v5.7)

CREATE TABLE OnSite
    (`uid` varchar(55), `worksite_id`  varchar(55), `timestamp` datetime)
;

INSERT INTO OnSite
    (`uid`, `worksite_id`, `timestamp`)
VALUES
  ("u12345", "worksite_1", '2019-01-01'),
  ("u12345", "worksite_1", '2019-01-02'),
  ("u12345", "worksite_1", '2019-01-03'),
  ("u12345", "worksite_1", '2019-01-04'),
  ("u12345", "worksite_1", '2019-01-06'),
  ("u1", "worksite_1", '2019-01-01'),
  ("u1", "worksite_1", '2019-01-02'),
  ("u1", "worksite_1", '2019-01-05'),
  ("u1", "worksite_1", '2019-01-06')

;

Query #1

SELECT    uid, COUNT(DISTINCT timestamp) Total
FROM      OnSite
GROUP BY  uid;

| uid    | Total |
| ------ | ----- |
| u1     | 4     |
| u12345 | 5     |

View on DB Fiddle


回答1:

Below is for BigQuery Standard SQL

In case if you are interested in max consecutive days of the users on the same worksite:

#standardSQL
SELECT uid, MAX(consecuitive_days) max_consecuitive_days
FROM (
  SELECT uid, grp, COUNT(1) consecuitive_days
  FROM (
    SELECT uid, 
      COUNTIF(step > 1) OVER(PARTITION BY uid, worksite_id ORDER BY ts) grp
    FROM (
      SELECT uid, worksite_id, ts, 
        DATE_DIFF(ts, LAG(ts) OVER(PARTITION BY uid, worksite_id ORDER BY ts), DAY) step 
      FROM `project.dataset.table`
    )
  ) GROUP BY uid, grp
) GROUP BY uid  

In case if worksite does not matter and you are looking just for max consecutive days:

#standardSQL
SELECT uid, MAX(consecuitive_days) max_consecuitive_days
FROM (
  SELECT uid, grp, COUNT(1) consecuitive_days
  FROM (
    SELECT uid, 
      COUNTIF(step > 1) OVER(PARTITION BY uid ORDER BY ts) grp
    FROM (
      SELECT uid, ts, 
        DATE_DIFF(ts, LAG(ts) OVER(PARTITION BY uid ORDER BY ts), DAY) step 
      FROM `project.dataset.table`
    )
  ) GROUP BY uid, grp
) GROUP BY uid  

You can test, play any of above with he sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'u12345' uid, 'worksite_1' worksite_id, DATE '2019-01-01' ts UNION ALL
  SELECT 'u12345', 'worksite_1', '2019-01-02' UNION ALL
  SELECT 'u12345', 'worksite_1', '2019-01-03' UNION ALL
  SELECT 'u12345', 'worksite_1', '2019-01-04' UNION ALL
  SELECT 'u12345', 'worksite_1', '2019-01-06' UNION ALL
  SELECT 'u1', 'worksite_1', '2019-01-01' UNION ALL
  SELECT 'u1', 'worksite_1', '2019-01-02' UNION ALL
  SELECT 'u1', 'worksite_1', '2019-01-05' UNION ALL
  SELECT 'u1', 'worksite_1', '2019-01-06' 
)
SELECT uid, MAX(consecuitive_days) max_consecuitive_days
FROM (
  SELECT uid, grp, COUNT(1) consecuitive_days
  FROM (
    SELECT uid, 
      COUNTIF(step > 1) OVER(PARTITION BY uid ORDER BY ts) grp
    FROM (
      SELECT uid, ts, 
        DATE_DIFF(ts, LAG(ts) OVER(PARTITION BY uid ORDER BY ts), DAY) step 
      FROM `project.dataset.table`
    )
  ) GROUP BY uid, grp
) GROUP BY uid   

with result:

Row uid     max_consecuitive_days    
1   u12345  4    
2   u1      2    

回答2:

does this fit for you?

set @gr=1;
select uid, max(cnt) max_cnt from (
  select uid, grp, count(*) cnt from (
    select uid, 
      case when ifnull(DATE_ADD(oldDate, INTERVAL 1 DAY), timestamp)= timestamp then 
        @gr 
      else  
        @gr := @gr +1 
      end grp
    from
    (
        SELECT    
          uid, 
          timestamp, 
          lag(timestamp) over (partition by uid order by timestamp asc) as oldDate
        FROM      OnSite
    ) t
  )t2
  group by uid, grp
  )t3
group by uid

Result

| uid    | max_cnt |
| ------ | ------- |
| u1     | 2       |
| u12345 | 4       |

DB Fiddle

  • 发表于 2019-01-22 00:25
  • 阅读 ( 280 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除