MySQL使用lag（）函数时忽略null值

发布于 2025-02-01 02:48:56 字数 1043 浏览 2 评论 0原文

我想计算全年发生的付款月份差异。例如，如果我在2月份有100款付款，＆amp; 120在三月份，差异将为20。我已经做了一个使用lag（）的查询，但是我面临的唯一问题是查询显示为无效值。由于2月之前没有付款，所以月botonth将显示为null，我想跳过该行。以下是示例数据集

，这是我正在使用的查询，

SELECT date_format(payment_date,'%M') 'Month', COUNT(*) - LAG(COUNT(*)) 
OVER (ORDER BY FIELD(date_format(payment_date,'%M'),
'January','February','March','April','May','June','July','August','September','October','November','December')) 
AS 'MonthByMonthChange'
from main
GROUP BY date_format(payment_date,'%M')
ORDER BY FIELD(date_format(payment_date,'%M'),'January','February','March','April','May','June','July','August','September','October','November','December');

还附加了要获得的输出。

原文

I want to calculate the MonthByMonth difference of payments happening throughout the year. For example, if I had 100 payments in February & 120 in the month of March, the difference would be 20. I have already made a query that uses LAG(), but the only problem I'm facing is that the query is showing NULL Value. Since there are no payments before February, the MonthByMonth would show NULL, I WANT TO SKIP THAT ROW.
Below is the sample Dataset

And this is the query I'm using

SELECT date_format(payment_date,'%M') 'Month', COUNT(*) - LAG(COUNT(*)) 
OVER (ORDER BY FIELD(date_format(payment_date,'%M'),
'January','February','March','April','May','June','July','August','September','October','November','December')) 
AS 'MonthByMonthChange'
from main
GROUP BY date_format(payment_date,'%M')
ORDER BY FIELD(date_format(payment_date,'%M'),'January','February','March','April','May','June','July','August','September','October','November','December');

Also attaching the output I'm getting.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮云落日 2025-02-08 02:48:56

子查询，然后在按月更改字段上添加检查null记录。

WITH cte AS (
    SELECT DATE_FORMAT(payment_date, '%M') Month,
           COUNT(*) - LAG(COUNT(*)) OVER (
               ORDER BY FIELD(DATE_FORMAT(payment_date, '%M'),
               'January', 'February', 'March', 'April', 'May', 'June', 'July',
               'August', 'September', 'October', 'November', 'December'))
           AS MonthByMonthChange
    FROM main
    GROUP BY 1
)

SELECT Month, MonthByMonthChange
FROM cte
WHERE MonthByMonthChange IS NOT NULL
ORDER BY FIELD(Month, 'January', 'February', 'March', 'April', 'May', 'June',
                      'July', 'August', 'September', 'October', 'November',
                      'December');

Subquery and then add a check on the month by month change field to filter off NULL records.

WITH cte AS (
    SELECT DATE_FORMAT(payment_date, '%M') Month,
           COUNT(*) - LAG(COUNT(*)) OVER (
               ORDER BY FIELD(DATE_FORMAT(payment_date, '%M'),
               'January', 'February', 'March', 'April', 'May', 'June', 'July',
               'August', 'September', 'October', 'November', 'December'))
           AS MonthByMonthChange
    FROM main
    GROUP BY 1
)

SELECT Month, MonthByMonthChange
FROM cte
WHERE MonthByMonthChange IS NOT NULL
ORDER BY FIELD(Month, 'January', 'February', 'March', 'April', 'May', 'June',
                      'July', 'August', 'September', 'October', 'November',
                      'December');

回复收藏 0 原文

~没有更多了~