SQLite如何查询账户月度收益下降的对应记录

解决方案 #

使用窗口函数LAG()获取同账户上一期的月度收益，再筛选出当期收益低于上期的记录即可，适配MySQL 8.0+、PostgreSQL、Oracle等支持窗口函数的数据库：

WITH monthly_revenue AS (
    -- 保留原有按账户+月度去重的逻辑
    SELECT account_id,
           monthly_date,
           earnings
    FROM accounts_revenue
    GROUP BY account_id, monthly_date
),
prev_earnings_calc AS (
    SELECT account_id,
           monthly_date,
           earnings,
           -- 按账户分组、按月份排序，取上一行的收益作为上期收益
           LAG(earnings) OVER (PARTITION BY account_id ORDER BY monthly_date) AS last_month_earnings
    FROM monthly_revenue
)
-- 仅保留收益较上期下降的记录
SELECT account_id, monthly_date, earnings
FROM prev_earnings_calc
WHERE earnings < last_month_earnings;

如果使用不支持CTE的低版本数据库，可以改用嵌套子查询实现：

SELECT account_id, monthly_date, earnings
FROM (
    SELECT account_id,
           monthly_date,
           earnings,
           LAG(earnings) OVER (PARTITION BY account_id ORDER BY monthly_date) AS last_month_earnings
    FROM (
        SELECT account_id, monthly_date, earnings
        FROM accounts_revenue
        GROUP BY account_id, monthly_date
    ) t1
) t2
WHERE earnings < last_month_earnings;

逻辑说明 #

• PARTITION BY account_id确保所有计算在单个账户的维度内执行，不会跨账户取数
• ORDER BY monthly_date保证收益对比的时间顺序正确，严格取当前月的上一个统计月的收益做对比
• 最终筛选条件自动剔除收益回升、持平的记录，仅保留下降节点的收益数据

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