Sql涉及迭代數據的問題怎么處理

本篇內容主要講解“Sql涉及迭代數據的問題怎么處理”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“Sql涉及迭代數據的問題怎么處理”吧!

1.生產問題原始描述

關于給用戶打標簽的問題，對于標簽的數據源在實際環境下幾乎是覆蓋全部門甚至全公司的，有的數據源本身是明細方式，有的是原始ods日志方式，有的是維度表，有的是名單表，對于需求方來說要的是快速迭代上線標簽，看需求或者數據倉庫劃分或者處理麻煩程度，往往會采用不同的處理方式，另外對于標簽的應用方來說一定會有人查值，值查人，資產分析等，本文的例子就一個名單表，什么是名單表呢，就是圈客的結果，比如滿足某條件某條件的一堆id，標簽天然就是為滿足圈客用的，但是原始數據它是圈客的結果，所以同時為了使得標簽在業務方使用起來更方便，所以把名單表加工成標簽，那么需求就確定了，從名單表里面加工出標簽。

原始表如下

# id  dt
1   10
2   10
2   11
3    11

解釋，為了說明更簡單，假設日期類型都用數字來表示，每天會有一群去重id，下一天的id更這一天的id必然是兩個交叉圓的形式，即既有多出來的，又有少出來的，又有重疊的。

現在需求就是，

我要篩選任意時間段的用戶，滿足是這段時間首次有效，或者無效，或者存量有效，分別對應描述里面的多出來，少了重合的每天

至于有效的定義就是，前一天名單沒有這個id，下一天有這個id，這就是首次有效，但是對于名單這種數據來說，一定會有有效，無效，有效，無效，這種,所以標簽的每天分片里面會有昨天的id加今天的id.

2.生產問題分析拆解

針對上面分析到的問題，有效無效這種狀態不能是最近一次，或者首次的有效，因為會有區段的概念，比如某歷史一段時間，只要首次出現在這段時間的，而且多天重合的。所以針對這個問題，我這邊處理成每天相對于前一天的有效狀態，分別為0，1，2，也即對應無效，首次有效，存量有效，另外對應每一段的信息。

所以這樣問題就化解為，先求有效狀態，然后再在有效狀態的基礎上，加工出最早時間和最晚時間，為了篩選方便，我另外加工出兩個冗余子屬性字段，是否首次有效和是否當前有效,對于有效無效，如果用窗口函數，找到同一個id的前一個日期，如果是日期的前一天，即表示2，存量有效，如果不為前一天則為增量有效，但是這種方式沒法把無效的取出來，所以我后面想到的是用full join來實現，這樣即可以把前一天后一天的數據都拿到。

3.問題解決

-- 對于有效無效的加工
with t3 as (
select id,dt,CASE
                  WHEN a.did IS NOT NULL
                          AND b.did IS NULL THEN 1
                       WHEN a.did IS NULL
                           AND b.did IS NOT NULL THEN 0
                     WHEN a.did IS NOT NULL
                           AND b.did IS NOT NULL THEN 2
                           end as is_valid
 from t1 a full join b on a.id = b.id
 )

-- 最早時間12，最晚時間14，是否首次有效，是否當前有效
select id,min(dt) as min_dt,max(dt) as max_dt, gid from (
select id,dt,is_valid, (dt - row_number() over (partition by id order by dt)) gid from t3 where is_valid != 0
) tmp group by id,gid

4.總結套路

4.1.首先思考可以用到的日期函數

datediff, date_sub/date_add

4.2.連續日期

連續問題都會用到一個排名函數，但是排名函數的值是數值，要與日期的連續性做到映射，才方便分組，比如可以把日期映射到連續數字，或者數字映射到連續日期，實現這兩個的操作就是通過前面的datedff 和 date_sub組合，原理就是日期與日期相減即可得到連續整數，整數隨便與某個日期做相減即可得到連續的日期,其中date_sub可以是反向排序得到連續日期。

4.3.通過連續的排序日期或者排序id相減，然后分組gid

即可解決此類問題

求連續狀態時間的開始和結束時間

4.4 對于特殊問題，比如想知道前后分區的數據

現有方式，一種是lag和lead窗口函數，另外一種是自表關聯，效率應該窗口函數高一點，基于本題，由于名單表的特殊原因，如果再給全量id打標簽，這樣結果分區的數據和原分區的數據不同，是加上了前一天失效的用戶，所以用的自表fulljoin關聯。

到此，相信大家對“Sql涉及迭代數據的問題怎么處理”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！