Sql涉及迭代數(shù)據(jù)的問題怎么處理

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

1.生產(chǎn)問題原始描述

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

原始表如下

# id  dt
1   10
2   10
2   11
3    11

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

現(xiàn)在需求就是，

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

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

2.生產(chǎn)問題分析拆解

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

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

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.總結(jié)套路

4.1.首先思考可以用到的日期函數(shù)

datediff, date_sub/date_add

4.2.連續(xù)日期

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

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

即可解決此類問題

求連續(xù)狀態(tài)時間的開始和結(jié)束時間

4.4 對于特殊問題，比如想知道前后分區(qū)的數(shù)據(jù)

現(xiàn)有方式，一種是lag和lead窗口函數(shù)，另外一種是自表關(guān)聯(lián)，效率應(yīng)該窗口函數(shù)高一點，基于本題，由于名單表的特殊原因，如果再給全量id打標簽，這樣結(jié)果分區(qū)的數(shù)據(jù)和原分區(qū)的數(shù)據(jù)不同，是加上了前一天失效的用戶，所以用的自表fulljoin關(guān)聯(lián)。

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