大家好，我是“蔣點(diǎn)數(shù)分”，多年以來(lái)一直從事數(shù)據(jù)分析工作。從今天開(kāi)始，與大家持續(xù)分享關(guān)于數(shù)據(jù)分析的學(xué)習(xí)內(nèi)容。

本文是第 6 篇，也是【SQL 周周練】系列的第 5 篇。該系列是挑選或自創(chuàng)具有一些難度的 SQL 題目，一周至少更新一篇。后續(xù)創(chuàng)作的內(nèi)容，初步規(guī)劃的方向包括：

后續(xù)內(nèi)容規(guī)劃

1.利用 Streamlit 實(shí)現(xiàn) Hive 元數(shù)據(jù)展示、SQL 編輯器、 結(jié)合Docker 沙箱實(shí)現(xiàn)數(shù)據(jù)分析 Agent

2.時(shí)間序列異常識(shí)別、異動(dòng)歸因算法3.留存率擬合、預(yù)測(cè)、建模

4.學(xué)習(xí) AB 實(shí)驗(yàn)、復(fù)雜實(shí)驗(yàn)設(shè)計(jì)等

5.自動(dòng)化機(jī)器學(xué)習(xí)、自動(dòng)化特征工程

6.因果推斷學(xué)習(xí)

7. ……

歡迎關(guān)注，一起學(xué)習(xí)。

第 5 期題目

題目來(lái)源：改編題目，在某 Excel 論壇看到的；求助希望使用 VBA 處理（求助者自稱(chēng)手工處理，上千條數(shù)據(jù)就需要做一天）。我參照他的數(shù)據(jù)格式改編為 SQL 題目（我的數(shù)據(jù)完全由自己模擬生成，并沒(méi)有使用求助者的數(shù)據(jù)）

一、題目介紹

有一張記錄了電表繳費(fèi)數(shù)據(jù)的表，繳費(fèi)的時(shí)間間隔是不固定的。領(lǐng)導(dǎo)希望將這張表重新拆解按照月份來(lái)匯總，分析每月的日均使用電量等等。求助者的原始數(shù)據(jù)還有站點(diǎn)編碼、電表號(hào)、供電類(lèi)型等等，我這里將問(wèn)題簡(jiǎn)化，省略這些信息，就假設(shè)只有一個(gè)電表。

注：只有繳費(fèi)區(qū)間的總電量，沒(méi)有每日電量；利用平均值拆分到每天再根據(jù)不同區(qū)間匯總到自然月。針對(duì)最開(kāi)始的日期和結(jié)束日期，如果不滿(mǎn)整月，就按照實(shí)際存在的日期區(qū)間來(lái)計(jì)算（時(shí)間維度按日來(lái)處理，不考慮小時(shí)等更精細(xì)的級(jí)別 | 求助者給的數(shù)據(jù)也是精確到日期而不是小時(shí)）

部分樣例數(shù)據(jù)（完整生成邏輯參見(jiàn)第三節(jié)）

二、題目思路

想要答題的同學(xué)，可以先思考答案??。

.……

.……

.……

我來(lái)談?wù)勎业乃悸?，這道題目要解決兩個(gè)問(wèn)題點(diǎn)：

1.根據(jù)整個(gè)電費(fèi)繳納涵蓋的區(qū)間，生成自然月的“維度表”。暴力的方法，那就是取最大最小日期，然后構(gòu)造一個(gè)遞增序列求出每一天，再分組/去重匯總成月，但是太低效了。直接的方法是獲取最大最小日期之間有多少個(gè)月，然后 add_months 加上去，除了最大最小日期的端點(diǎn)，取出每一個(gè)月的 1 號(hào)和當(dāng)月最后一天

是不是一定要做這一步？實(shí)際工作中可以使用固定日期范圍或者數(shù)倉(cāng)里現(xiàn)成的維度表 dim_date 等等。但是動(dòng)態(tài)日期維度表生成一方面更符合這個(gè)場(chǎng)景的邏輯；另一方面也是筆試的考點(diǎn)，只不過(guò)動(dòng)態(tài)生成每一天日期更常見(jiàn)。

2.根據(jù)上一步獲取的自然月區(qū)間，來(lái)與繳費(fèi)區(qū)間關(guān)聯(lián)，關(guān)聯(lián)的條件是存在交集。這又涉及到兩點(diǎn)，第一是如何判斷有交集（包括哪怕只有端點(diǎn)一天相交），第二是如何取交集區(qū)間的數(shù)據(jù)來(lái)進(jìn)行后續(xù)的計(jì)算

（目前【SQL 周周練】系列默認(rèn)討論 Hive，但很多思路可以移植到其他 SQL 方言；我了解到諸如 Oracle 和 Postgre 是有非常多函數(shù)的，可能相對(duì)于 Hive 會(huì)有更直接更好用的方法 | SparkSQL 一般都能兼容 Hive，后期我會(huì)逐步拓展到 MySQL、DuckDB、Doris/Starrocks）

下面，我用 NumPy 和 Scipy 生成模擬的數(shù)據(jù)集：

三、用 Python 生成模擬數(shù)據(jù)

只關(guān)心 SQL 代碼的同學(xué)，可以跳轉(zhuǎn)到第四節(jié)（我在工作中使用 Hive 較多，因此采用 Hive 的語(yǔ)法）

模擬代碼如下：

1.構(gòu)造日期間隔，并將順序隨機(jī)打亂，作為“繳費(fèi)”日期區(qū)間：

import numpy as np
import pandas as pd
from datetime import datetime, timedelta

# 確定隨機(jī)數(shù)種子
np.random.seed(2025)

# 構(gòu)造日期間隔
date_interval_list = [1, 3, 7, 14, 15, 28, 30, 45, 90] * 2

# 打亂順序，注意它是直接修改沒(méi)有返回
np.random.shuffle(date_interval_list)  
print(date_interval_list)

2.隨機(jī)抽樣生成“繳費(fèi)”區(qū)間的日耗電量，構(gòu)造“繳費(fèi)”區(qū)間的起始日期、結(jié)束日期。將前面生成的數(shù)據(jù)轉(zhuǎn)為 pd.DataFrame，并輸出為 csv 文件：

power_consumption = range(50, 510, 10)

# 隨機(jī)抽取數(shù)值，作為繳費(fèi)區(qū)間的平均日消耗電量
daily_electricity_usage = np.random.choice(
    power_consumption, size=len(date_interval_list), replace=False
)

df = pd.DataFrame(
    {
        "period_start": [
            datetime(2023, 12, 1) + timedelta(days=sum(date_interval_list[:i]) )
            for i in range(len(date_interval_list))
        ],
        "period_end": [
            datetime(2023, 12, 1) + timedelta(days=sum(date_interval_list[:i])-1)
            for i in range(1, len(date_interval_list) + 1)
        ],
        "electricity_usage": date_interval_list * daily_electricity_usage,
        "daily_electricity_usage": daily_electricity_usage,
        "period": date_interval_list,
    }
)
# 在 Jupyter 環(huán)境中顯示 dataframe，其他環(huán)境執(zhí)行可能報(bào)錯(cuò)
display(df)

out_csv_path = "dwd_electricity_usage_records.csv"
df.to_csv(out_csv_path, index=False, header=False, encoding="utf-8-sig")

3.如果表存在則刪除，創(chuàng)建新的 Hive 表，并將數(shù)據(jù) load 到表中：

from pyhive import hive

# 配置連接參數(shù)
host_ip = "127.0.0.1"
port = 10000
username = "Jiang"


with hive.Connection(host=host_ip, port=port) as conn:
    cursor = conn.cursor()

    hive_table_name = 'data_exercise.dwd_electricity_usage_records'

    drop_table_sql = f"""
    drop table if exists {hive_table_name}
    """

    print('刪除表語(yǔ)句：\n', drop_table_sql)

    cursor.execute(drop_table_sql)

    create_table_sql = f"""
    create table if not exists `{hive_table_name}` (
        period_start string comment '費(fèi)用期始',
        period_end string comment '費(fèi)用期終',
        electricity_usage int comment '計(jì)費(fèi)期間總用電量',
        daily_electricity_usage int comment '計(jì)費(fèi)期間日均電量（用來(lái)核對(duì)結(jié)果）',
        period int comment '期間天數(shù)'
    )
    comment "不定期的電表計(jì)費(fèi)數(shù)據(jù) | author: 蔣點(diǎn)數(shù)分 | 文章編號(hào)：04d08f61"
    row format delimited fields terminated by ','
    stored as textfile
    """

    print("創(chuàng)建表語(yǔ)句：\n", create_table_sql)
    cursor.execute(create_table_sql)

    import os

    load_data_sql = f"""
    load data local inpath "{os.path.abspath(out_csv_path)}"
    overwrite into table {hive_table_name}
    """

    print("覆蓋寫(xiě)入數(shù)據(jù)語(yǔ)句：\n", load_data_sql)
    cursor.execute(load_data_sql)

    cursor.close()

我通過(guò)使用 PyHive 包實(shí)現(xiàn) Python 操作 Hive。我個(gè)人電腦部署了 Hadoop 及 Hive，但是沒(méi)有開(kāi)啟認(rèn)證，企業(yè)里一般常用 Kerberos 來(lái)進(jìn)行大數(shù)據(jù)集群的認(rèn)證。

四、SQL 解答

我使用 CTE 的語(yǔ)法，這樣將步驟串行展示，邏輯比較清晰，下面分成幾部分解釋 SQL 語(yǔ)句：

1.這部分代碼的邏輯是，先求出所有繳費(fèi)區(qū)間最大最小的日期；然后利用求出來(lái)兩個(gè)日期之間的有多少自然月（包含兩個(gè)端點(diǎn)所在的自然月）；有的同學(xué)會(huì)問(wèn)，Hive 中是存在 months_between 的，為什么你不用，而要自己“年-年”、“月-月”的這么算？當(dāng)然可以用，但是要額外處理一下，此代碼塊后面，我解釋一下，如何額外處理：

with get_date_interval as (
    -- 獲取總體的開(kāi)始、結(jié)束日期：這里只是一個(gè)電表
    -- 多個(gè)電表注意后續(xù)的邏輯該帶分組的分組
    select
      min(period_start) as all_start
    , max(period_end) as all_end
    from data_exercise.dwd_electricity_usage_records
)
, get_inner_months as (
    -- 獲取兩個(gè)日期之間有多少個(gè)月；“年-年”*12“+ 月-月”
    -- 請(qǐng)參看文章的解釋?zhuān)蝗绻凑铡皵?shù)數(shù)”的規(guī)則，注意結(jié)果加 1
    -- 如果要用 months_between 函數(shù)，該怎么處理？
    -- 根據(jù)官方文檔，如果 兩個(gè)日期的 day 部分不一樣或者不是當(dāng)月的最后一天
    -- 則是按照 31 天的月份來(lái)計(jì)算小數(shù)部分 `fractional portion`
    -- 注意 months_between 返回的小數(shù)，要取整
    select
      all_start, all_end
    , (year(all_end) - year(all_start))*12 + month(all_end) - month(all_start) + 1 as month_cnt
    -- , int(months_between(last_day(all_start), last_day(all_start)))+1 as month_cnt
    from get_date_interval
)
……

關(guān)于 months_between 函數(shù)，我查看了 Hive 的官方文檔，人家是這么說(shuō)的 —— “If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the UDF calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.” 翻譯后是 “如果 date1 和 date2 在當(dāng)月的日期是相同的，或者都是各自月份的最后一天 ，那么結(jié)果始終是一個(gè)整數(shù)。否則，該函數(shù)會(huì)基于一個(gè)31天的月份來(lái)計(jì)算結(jié)果的小數(shù)部分，并考慮 date1 和 date2 的時(shí)間部分差異。”

我沒(méi)有看源代碼，但是這里面說(shuō)的是 fractional portion 小數(shù)部分按照 31 天的月來(lái)處理，什么意思呢？我稍微實(shí)驗(yàn)了一下，小數(shù)部分應(yīng)該就是 (day(...)-day(...))/31（負(fù)數(shù)就向前借位），它的結(jié)果是四舍五入保留 8 位數(shù)。我們想避開(kāi)這個(gè)小數(shù)，就想讓它返回整數(shù)月份怎么辦呢（因?yàn)榍闆r比較多，單獨(dú)用向上取整、向下取整或者四舍五入取整都有問(wèn)題，要綜合判斷，直接返回整數(shù)月份最好）

我這里的最大最小日期，大概率 day 部分是不一樣的，也不太可能都是每個(gè)月份的最后一天。這樣會(huì)涉及小數(shù)，那么咱手工處理一下 —— 比如將最大最小日期改成當(dāng)月的最后一天，這個(gè)很簡(jiǎn)單，使用 last_day 函數(shù)即可；還可以改成當(dāng)月的第一天，使用 trunc(...,'MM')，也滿(mǎn)足使用條件。甚至咱追求個(gè)性，將日期的年月部分取出來(lái)，日部分都寫(xiě)個(gè) 5 號(hào)、13 號(hào)也可以，只要不超過(guò) 28 號(hào)，就不用考慮日期合法性問(wèn)題。

圖片里 m1 到 m4 的寫(xiě)法都是可以的，只不過(guò)后面要轉(zhuǎn)換為整數(shù)。

2.使用 repeat 和 split 加上 posexplode 構(gòu)造一個(gè)指定長(zhǎng)度的遞增序列，這也是 Hive 的常見(jiàn)技巧。explode 更常見(jiàn)，posexplode 就是多返回一個(gè)“位置”列。（我個(gè)人電腦部署的 Hive 版本是 3.1.3，我驗(yàn)證過(guò)沒(méi)有 sequence 函數(shù)）。如果根據(jù)日期取某個(gè)自然月的 1 號(hào)和最后一天，上一小節(jié)我已經(jīng)解釋過(guò)；注意最早最晚日期根據(jù)我題目的定義，要按照實(shí)際日期來(lái)計(jì)算：

……
, get_every_month_start as (
    select
     (case
        -- 對(duì)于非開(kāi)始日期的那個(gè)月，取當(dāng)月的 1 號(hào)
        when idx > 0 then add_months(trunc(g.all_start, 'MM'), idx)
        -- 如果是開(kāi)始日期，直接用開(kāi)始日期，不管它是不是當(dāng)月的 1 號(hào)
        when idx = 0 then g.all_start 
     end) as month_start
    , all_end
    from get_inner_months g
    lateral view posexplode(split(repeat(',', month_cnt-1), ',')) month_table as idx, m
)
, get_every_month_end as (
    select
      month_start
      -- 如果是結(jié)束日期的那個(gè)月，結(jié)束日期就取截止日期
      -- 否則取每個(gè)月的最后一天，即 last_day
    , if(date_format(month_start, 'yyyy-MM') = date_format(all_end, 'yyyy-MM'), all_end,
    last_day(month_start)) as month_end
    from get_every_month_start
)
……

3.這一步是將前面構(gòu)造的自然月維度表 get_every_month_end 與電費(fèi)數(shù)據(jù)集，進(jìn)行關(guān)聯(lián)。關(guān)聯(lián)的條件是判斷有交集，為什么這兩個(gè)條件可以保證有交集，我在代碼下面用圖片結(jié)合文字論述。另外就是當(dāng)兩者有交集的情況下，取交集的邏輯是 —— 起點(diǎn)選擇最晚 greatest 的那個(gè)，終點(diǎn)選擇最早 least 的那個(gè)。

Hive 非常早期的版本是不支持不等值連接的。這個(gè)時(shí)候要小心處理 on 后面的條件，如果是 inner join 一般將 on 后面的條件移到 where 中，而 outer join 如果 on 不匹配，只是代表關(guān)聯(lián)的表沒(méi)有匹配的數(shù)據(jù)，可以理解為給你一個(gè) null；比如拿 left outer join 舉例，左表的數(shù)據(jù)是不會(huì)被 on 條件過(guò)濾掉的，如果 on 能導(dǎo)致一條數(shù)據(jù)匹配多條，左表的部分?jǐn)?shù)據(jù)甚至全部數(shù)據(jù)還會(huì)翻倍上漲。這個(gè)時(shí)候，不能將 on 的條件直接移到 where 中，而是需要保持 on 里面的最粗糙的匹配邏輯（這個(gè)場(chǎng)景不行，因?yàn)閮蓚€(gè)條件都是不等值，需要人工造一個(gè)無(wú)用的關(guān)聯(lián)），在 select 中對(duì)于右表的列，在使用時(shí)進(jìn)行 on 條件的完整判斷 if，如果滿(mǎn)足，返回右表列的值；否則返回 null。這種方法缺點(diǎn)是每一個(gè)用到右表的列，都需要這么處理。

……
, get_interval_power_usage as (
    select
      e.month_start as month_start
    , e.month_end as month_end
    -- 注意取兩個(gè)日期區(qū)間的交集
    -- 非常重要，別忘了取區(qū)間的交集
    -- 起點(diǎn)取最后面的那個(gè)，終點(diǎn)取最早的那個(gè)
    , greatest(r.period_start,e.month_start) as period_start
    , least(r.period_end,e.month_end) as period_end
    , nvl(r.daily_electricity_usage,0) as daily_electricity_usage
    from get_every_month_end e
    left outer join data_exercise.dwd_electricity_usage_records r
        on r.period_end >= e.month_start
        and r.period_start <= e.month_end
)
……

4.這是最后求結(jié)果的 SQL 部分，注意前面取交集后，會(huì)劃分稱(chēng)成若干“子”區(qū)間，每個(gè)區(qū)間計(jì)算時(shí)用原來(lái)的日均值乘以區(qū)間長(zhǎng)度（注意 datediff 結(jié)果加 1）計(jì)算該區(qū)間的總電量消耗。group by 加和整月的電量，整月的長(zhǎng)度可以用子區(qū)間長(zhǎng)度疊加 days_cnt 列，也可以直接求 days_cnt2 列，注意我這里的 month_start，month_end 在最開(kāi)始與最晚日期是根據(jù)實(shí)際日期定義的，因此可以用 days_cnt2 的邏輯。

另外，這里只靠 month_start 就足以標(biāo)記統(tǒng)計(jì)的自然月，沒(méi)必要將 month_end 也作為 group by 的分組標(biāo)識(shí)，用 max/min 當(dāng)成維度值處理，這也是最基本的技巧。（我已經(jīng)驗(yàn)證Hive 3.1.3 也沒(méi)有 any_value）在數(shù)據(jù)量很大時(shí)或者分組 key 比較多時(shí)，有助于提高計(jì)算速度、減少資源消耗：

……
select
  month_start
, max(month_end) as month_end
  
, sum(daily_electricity_usage * (datediff(period_end, period_start)+1)) 
    / sum(datediff(period_end, period_start)+1) as new_daliy_usage
-- 用哪個(gè)做分母都行    
-- , sum(datediff(period_end, period_start)+1) as days_cnt
--, max(datediff(month_end, month_start)+1) as days_cnt2
from get_interval_power_usage
group by month_start 

5.完整的 SQL 語(yǔ)句：

with get_date_interval as (
    -- 獲取總體的開(kāi)始、結(jié)束日期：這里只是一個(gè)電表
    -- 多個(gè)電表注意后續(xù)的邏輯該帶分組的分組
    select
      min(period_start) as all_start
    , max(period_end) as all_end
    from data_exercise.dwd_electricity_usage_records
)
, get_inner_months as (
    -- 獲取兩個(gè)日期之間有多少個(gè)月；為什么不用 months_between
    -- 請(qǐng)參看文章的解釋?zhuān)蝗绻凑铡皵?shù)數(shù)”的規(guī)則，注意結(jié)果加 1
    select
      all_start, all_end
    , (year(all_end) - year(all_start))*12 + month(all_end) - month(all_start) + 1 as month_cnt
    from get_date_interval
)
, get_every_month_start as (
    select
     (case
        -- 對(duì)于非開(kāi)始日期的那個(gè)月，取當(dāng)月的 1 號(hào)
        when idx > 0 then add_months(trunc(g.all_start, 'MM'), idx)
        -- 如果是開(kāi)始日期，直接用開(kāi)始日期，不管它是不是當(dāng)月的 1 號(hào)
        when idx = 0 then g.all_start 
     end) as month_start
    , all_end
    from get_inner_months g
    lateral view posexplode(split(repeat(',', month_cnt-1), ',')) month_table as idx, m
)
, get_every_month_end as (
    select
      month_start
      -- 如果是結(jié)束日期的那個(gè)月，結(jié)束日期就取截止日期
      -- 否則取每個(gè)月的最后一天，即 last_day
    , if(date_format(month_start, 'yyyy-MM') = date_format(all_end, 'yyyy-MM'), all_end,
    last_day(month_start)) as month_end
    from get_every_month_start
)
, get_interval_power_usage as (
    select
      e.month_start as month_start
    , e.month_end as month_end
    -- 注意取兩個(gè)日期區(qū)間的交集
    -- 非常重要，別忘了取區(qū)間的交集
    -- 起點(diǎn)取最后面的那個(gè)，終點(diǎn)取最早的那個(gè)
    , greatest(r.period_start,e.month_start) as period_start
    , least(r.period_end,e.month_end) as period_end
    , r.daily_electricity_usage as daily_electricity_usage
    from get_every_month_end e
    left outer join data_exercise.dwd_electricity_usage_records r
        on r.period_end >= e.month_start
        and r.period_start <= e.month_end
)

select
  month_start
, max(month_end) as month_end
  
, sum(daily_electricity_usage * (datediff(period_end, period_start)+1)) 
    / sum(datediff(period_end, period_start)+1) as new_daliy_usage
-- 用哪個(gè)做分母都行    
-- , sum(datediff(period_end, period_start)+1) as days_cnt
--, max(datediff(month_end, month_start)+1) as days_cnt2
from get_interval_power_usage
group by month_start

<hr>

??????

我現(xiàn)在正在求職數(shù)據(jù)類(lèi)工作（主要是數(shù)據(jù)分析或數(shù)據(jù)科學(xué)）；如果您有合適的機(jī)會(huì)，即時(shí)到崗，不限城市。