英文:
LAST_DAY function in mysql is not working
问题
以下是翻译好的部分：
我在mysql中使用LAST_DAY()函数来获取每个月每个客户的最后一天的数值。我使用的是mysql 8.0版本。有人可以帮助我吗？示例表格如下：
示例代码：
select customer_id, LAST_DAY(txn_date) AS months, txn_type, txn_amount
from data_bank.customer_transactions
group by customer_id, months
order by customer_id
从上面的代码中，这是我得到的结果：
预期结果：
customer_id months txn_type txn_amount
1 2020-01-31 deposit 312
1 2020-03-31 purchase 664
英文:
Here I am using LAST_DAY() function to get last day of values from each month for each customer in mysql.
I am using mysql 8.0 version.
Could anyone please help me with this.
Sample table example:
sample code:
select customer_id, LAST_DAY(txn_date) AS months, txn_type, txn_amount
from data_bank.customer_transactions
group by customer_id, months
order by customer_id
From the above code this is the results I have obtained :
Expected results:
customer_id months txn_type txn_amount
1 2020-01-31 deposit 312
1 2020-03-31 purchase 664
答案1
得分: 1
以下是翻译好的内容：
你假设因为你按照月份的最后一天进行分组，它会返回其他列的最新行的值。这并不是事实。在一个列中返回的值与应用于其他列的函数毫无关系。
阅读这个链接：
然后使用类似这样的方法来返回每个日历月的最新行...
WITH
partitioned AS
(
SELECT
*,
ROW_NUMBER()
OVER (
PARTITION BY customer_id, LAST_DAY(txn_date)
ORDER BY txn_date DESC
)
AS month_row_id
FROM
data_bank.customer_transactions
)
SELECT
*
FROM
partitioned
WHERE
month_row_id = 1
英文:
You're assuming that because you group by the last day of the month, it will return the latest row's values for the other columns. That's not the case. The values returned in one column have nothing to do with the functions applied to other columns.
Read this:
Then use something like this to return the latest row per calendar month...
WITH
partitioned AS
(
SELECT
*,
ROW_NUMBER()
OVER (
PARTITION BY customer_id, LAST_DAY(txn_date)
ORDER BY txn_date DESC
)
AS month_row_id
FROM
data_bank.customer_transactions
)
SELECT
*
FROM
partitioned
WHERE
month_row_id = 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论