Bigquery歷史資料中非連續失敗的輸出數

這與我之前的情況有關。

我有一個這樣的資料集：

WITH failure_table AS
  (SELECT 'Andrea' AS name, 'Failure' AS status, '2022-04-28 4:00:00' AS timestamp
   UNION ALL SELECT 'Karl', 'Failure', '2022-04-28 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-04-27 4:00:00'
   UNION ALL SELECT 'Karl', 'Failure', '2022-04-27 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-04-26 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-04-25 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-30 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-29 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-28 4:00:00'
   UNION ALL SELECT 'Karl', 'Failure', '2022-03-28 4:00:00')
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-15 4:00:00')

除了輸出用戶第一次提交失敗的時間戳，并且每天連續提交失敗狀態，直到今天（2022-04-29），我還想輸出 Karl 的非連續天數或者安德里亞犯了一個錯誤。

在這種情況下，Andrea 最近在 2022-04-25 4:00:00 開始失敗，并提交了 3 個失敗塊（03-15、03-28 到 03-30、04-25 到 04-28），而 Karl 最近開始失敗在 2022-04-27 4:00:00 并提交 2 個失敗塊（03-28、04-27 到 04-28）。

最終輸出應該是

感謝那些可以提供幫助的人，我真的很感激。

uj5u.com熱心網友回復：

看看下面的查詢，雖然它還沒有完善。希望能幫助您找到解決問題的線索。

1. failure_blocks用于計算每個連續失敗的日子。
2. last_blocks用于查找最后一個失敗的塊來識別started_recently_failing_timestamp
3. 主查詢從以前的 CTE 生成預期的輸出。

WITH failure_table AS (
  SELECT 'Andrea' AS name, 'Failure' AS status, TIMESTAMP '2022-04-28 4:00:00' AS dt
   UNION ALL SELECT 'Karl', 'Failure', '2022-04-28 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-04-27 4:00:00'
   UNION ALL SELECT 'Karl', 'Failure', '2022-04-27 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-04-26 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-04-25 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-30 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-29 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-28 4:00:00'
   UNION ALL SELECT 'Karl', 'Failure', '2022-03-28 4:00:00'
   UNION ALL SELECT 'Andrea', 'Failure', '2022-03-15 4:00:00'
),
failure_blocks AS (
  SELECT *,
         COUNTIF(diff <> 1) OVER (PARTITION BY name) AS total_failure_blocks,
         COUNT(*) OVER (PARTITION BY name) AS total_days_failing,
         SUM(diff - 1) OVER (PARTITION BY name ORDER BY dt) AS block,
    FROM (
      SELECT name, status, dt, IFNULL(DATE_DIFF(dt, LAG(dt) OVER (PARTITION BY name ORDER BY dt), DAY), 0) AS diff
        FROM failure_table
    )
),
last_blocks AS (
SELECT * EXCEPT(diff, block), 
       COUNT(*) OVER (PARTITION BY name, block) AS recent_days_failing,
       FIRST_VALUE(dt) OVER (PARTITION BY name, block ORDER BY dt) AS block_start_dt
  FROM failure_blocks
)
SELECT name, status, 
       MAX(block_start_dt) OVER (PARTITION BY name) AS started_recently_failing_timestamp,
       recent_days_failing,
       total_days_failing,
       total_failure_blocks,
  FROM last_blocks 
 WHERE TRUE QUALIFY dt = started_recently_failing_timestamp
;

uj5u.com熱心網友回復：

也考慮下面的方法

select name, status,
  sum(if(rank = 1, consecutive_days, 0)) as recent_days_failing,
  sum(consecutive_days) as total_days_failing,
  count(block_id) as total_failure_block_ids
from (
  select name, status, block_id, 
    date_diff(max(dt), min(dt), day)   1 as consecutive_days,
    rank() over(partition by name, status order by block_id) rank
  from (
    select name, status, date(timestamp) dt,
      row_number() over(partition by name, status order by timestamp)   
      date_diff(current_date, date(timestamp), day) as block_id
    from failure_table
  )
  group by name, status, block_id
)
group by name, status   

如果應用于您問題中的樣本資料 - 輸出是

標籤：sql 数据库 约会时间 谷歌大查询 数据分析

上一篇：更新不推薦使用的代碼，從each()到foreach()

下一篇：以編程方式呈現VC