如何检查值是否在几个月内减少SQLite

Question

我每月都有收入，我想要的是查看每个账户的收入，从上次减少到递减顺序下面是查询

SELECT account_id,
monthly_date,
earnings
FROM accounts_revenue
GROUP BY account_id,
monthly_date

数据是这样的

tbody> <<tr>5555555555555555555555

account_id	monthly_date	earnings
55	2017-01-01	2000
	2017-02-01	1950
	2017-10-01	2000
	2018-02-01	1500
	2018-05-01	1200
	2018-12-01	3000
	2019-01-01	900
	2019-02-01	810
	2019-04-01	1000
	2019-05-01	600
	2020-01-01	800
	2020-02-01	100
122	2020-01-01	800
122	2020-02-01	100

使用NOT EXISTS:

SELECT ar1.*
FROM accounts_revenue ar1
WHERE NOT EXISTS (
SELECT 1
FROM accounts_revenue ar2
WHERE ar2.account_id = ar1.account_id 
AND ar2.monthly_date < ar1.monthly_date 
AND ar2.earnings <= ar1.earnings
)
ORDER BY ar1.account_id, ar1.monthly_date;

您可以使用lag()窗口函数和CTE(或子查询，如果您喜欢)来过滤掉您不想要的行:

WITH revenue AS
(SELECT account_id, monthly_date, earnings,
lag(earnings) OVER (PARTITION BY account_id ORDER BY monthly_date) AS prev_earnings
FROM accounts_revenue)
SELECT account_id, monthly_date, earnings
FROM revenue
WHERE earnings < prev_earnings OR prev_earnings IS NULL
ORDER BY account_id, monthly_date;

为了提高效率，您需要在accounts_revenue(account_id, monthly_date)上建立索引。