如何在Hive中判断当年同月销售额是否高于去年同月？现有SQL求指导

你已经用LAG(samount, 12)正确拿到了去年同月的销售额，只需要补充判断逻辑就能完成需求。可以用CASE WHEN语句根据销售额差值生成明确的判断结果：

SELECT 
    year,
    month,
    samount,
    LAG(samount, 12) OVER (ORDER BY year, month) AS prev_year_same_month_sales,
    samount - LAG(samount, 12) OVER (ORDER BY year, month) AS diff_cur_prev_sales,
    CASE
        WHEN LAG(samount, 12) OVER (ORDER BY year, month) IS NULL THEN '无去年同期数据'
        WHEN samount > LAG(samount, 12) OVER (ORDER BY year, month) THEN '高于去年同月'
        ELSE '不高于去年同月'
    END AS is_higher_than_last_year
FROM sales;

关键说明： #

• 新增prev_year_same_month_sales字段，直接显示去年同月的销售额，结果更直观
• 用CASE WHEN做三层判断：
  • 若去年同期数据为空（比如第一年的1-12月），标记为"无去年同期数据"
  • 若当期销售额大于去年同月，标记为"高于去年同月"
  • 其余情况（等于或小于）标记为"不高于去年同月"
• 要是觉得重复写LAG麻烦，也可以用CTE先计算基础值再判断，代码更简洁：

WITH sales_with_prev AS (
    SELECT 
        year,
        month,
        samount,
        LAG(samount, 12) OVER (ORDER BY year, month) AS prev_year_same_month_sales
    FROM sales
)
SELECT 
    *,
    samount - prev_year_same_month_sales AS diff_cur_prev_sales,
    CASE
        WHEN prev_year_same_month_sales IS NULL THEN '无去年同期数据'
        WHEN samount > prev_year_same_month_sales THEN '高于去年同月'
        ELSE '不高于去年同月'
    END AS is_higher_than_last_year
FROM sales_with_prev;

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