如何将MySQL SQL转换为Hive SQL？附每日有效店铺数统计需求

首先明确需求：我们需要基于ProductOrder表，统计当前日期（2018-04-27）往前一个月内每一天的有效店铺数量，有效店铺的判定标准是当天落在该店铺订单的starttime到endtime区间内。

需求数据回顾 #

原表ProductOrder结构及数据：

Hive SQL实现代码 #

WITH date_dim AS (
    -- 生成当前日期（2018-04-27）往前30天的所有日期
    SELECT 
        date_add('2018-04-27', -pos) AS dt
    FROM 
        (SELECT posexplode(split(space(29), ' ')) AS (pos, val)) t
    -- 确保只取最近一个月（30天）的日期，避免超出范围
    WHERE date_add('2018-04-27', -pos) >= date_add('2018-04-27', -29)
)
SELECT 
    d.dt AS day,
    COUNT(DISTINCT p.shopid) AS count
FROM 
    date_dim d
LEFT JOIN 
    ProductOrder p
ON 
    -- 判定当天是否在订单时间区间内
    to_date(p.starttime) <= d.dt 
    AND d.dt <= to_date(p.endtime)
GROUP BY 
    d.dt
ORDER BY 
    d.dt DESC;

关键逻辑说明 #

1. 生成日期维度表：
   Hive没有MySQL的递归CTE语法，所以我们用posexplode(split(space(29), ' '))生成30条连续记录（space(29)会生成29个空格，split后得到30个空字符串，pos从0到29），再通过date_add计算出从2018-04-27往前推的每一天。

2. 关联与筛选：
   用左连接确保每一天都出现在结果中（即使当天没有有效店铺，count会显示0），通过to_date()函数统一日期格式（如果你的starttime和endtime已经是date类型，可以去掉这个转换），判断日期是否在订单区间内。

3. 去重计数：
   用COUNT(DISTINCT p.shopid)确保同一个店铺即使有多个符合条件的订单，也只被统计一次。

内容的提问来源于stack exchange，提问作者yonney.yang