英文:
Aggregation of query in sql
问题
我有一个表格，如下：
| 编号 | 账户 | 日期 | 金额 |
|---|---|---|---|
| 1. | ad@ind | 2022.07.11 | 354 |
| 2. | ad@ind | 2022.11.10 | 586 |
我需要找到月份数字最大的记录，并获取该日期的金额。应该如何做？Max(date)不适用，我需要月份内的数字。
我尝试过按日期和金额进行分组，尝试过使用内部查询，但都没有成功。
英文:
I have table, like:
| Id | Account | Date | Amount |
|---|---|---|---|
| 1. | ad@ind | 07.11.2022 | 354 |
| 2. | ad@ind | 10.11.2022 | 586 |
I need to find record, where is maximum number of month and get amount in this date. How is it to do? Max(date) does'nt fit, i need number inside month.
I tried to do group by date and amount, i tried to do inner query, but it was'nt lucky.
答案1
得分: 1
使用窗口函数 ROW_NUMBER() 的以下解决方案假设您想要每个月的一条记录：
SELECT id, account, date, amount
FROM
(
SELECT yt.*,
ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) ORDER BY date DESC) as rn
FROM yourtable yt
)dt
WHERE rn = 1;
这个 ROW_NUMBER() 窗口函数特性生成一个从1开始的行号，用于具有相同的年份和月份的每一行，按 date 列降序排序。具有 rn 值为 1 的记录是该月/年组合的最高日期记录。
使用我在评论中分享的类似逻辑，但可能较慢，因为需要两次表扫描：
SELECT *
FROM yourtable yt
WHERE date =
(
SELECT max(date)
FROM yourtable yt2
WHERE
EXTRACT(MONTH FROM yt.date) = EXTRACT(MONTH FROM yt2.date)
AND EXTRACT(YEAR FROM yt.date) = EXTRACT(YEAR FROM yt2.date)
);
(Note: The provided SQL queries assume you have a table named "yourtable" with columns like "id," "account," "date," and "amount.")
英文:
Assuming you want a record for each month, then consider the following solution using window function ROW_NUMBER():
SELECT id, account, date, amount
FROM
(
SELECT yt.*,
ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) ORDER BY date DESC) as rn
FROM yourtable yt
)dt
WHERE rn = 1;
This ROW_NUMBER() window function feature is generating a row_number, starting at 1, for each row that shares the same month and year, ordering by the date column descending. The record that gets rn of 1 is the highest date for that month/year combination.
Using similar logic as I shared in the comment, which is likely slower since two table scans are needed:
SELECT *
FROM yourtable yt
WHERE date =
(
SELECT max(date)
FROM yourtable yt2
WHERE
EXTRACT(MONTH FROM yt.date) = EXTRACT(MONTH FROM yt2.date
AND EXTRACT(YEAR FROM yt.date) = EXTRACT YEAR FROM yt2.date)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论