Spark SQL如何实现跨月入住拆分的酒店月度入住人次统计

实现思路 #

核心是用Spark SQL内置的sequence函数生成入住记录覆盖的所有月份序列，再用explode函数将序列拆分为多行，无需手动分场景写UNION逻辑，代码简洁易维护。

具体实现SQL #

WITH hotel_stay AS (
    -- 第一步：把字符串格式的入离店日期转成日期类型，同时计算入离店对应的月份首日
    SELECT
        Hotel,
        Visitor,
        to_date(`In`, 'dd.MM.yyyy') AS in_date,
        to_date(`Out`, 'dd.MM.yyyy') AS out_date,
        date_trunc('month', to_date(`In`, 'dd.MM.yyyy')) AS in_month,
        date_trunc('month', to_date(`Out`, 'dd.MM.yyyy')) AS out_month
    FROM 你的输入表名
)
SELECT
    Hotel,
    date_format(month_series, 'yyyy-MM') AS stay_month,
    -- 同一访客同月多次入住需要去重用COUNT(DISTINCT Visitor)，不需要去重直接用COUNT(1)即可
    COUNT(DISTINCT Visitor) AS arrival_person_time
FROM hotel_stay
-- 生成从入住月份到离店月份的所有月份首日序列，间隔1个月后炸开成多行
LATERAL VIEW EXPLODE(sequence(in_month, out_month, INTERVAL 1 MONTH)) t AS month_series
GROUP BY Hotel, date_format(month_series, 'yyyy-MM')
ORDER BY stay_month;

样例验证 #

针对你给出的样例输入：

• John的入离店月份是2020-09和2020-10，sequence会生成2020-09-01、2020-10-01两个值，炸开后对应两条记录
• Mark的入离店月份都是2020-09，sequence只会生成2020-09-01一个值，对应一条记录

分组统计后结果为2020-09月2人次、2020-10月1人次，完全符合你给出的统计规则。

内容的提问来源于stack exchange，提问作者hankuk