如何获得不同项目的一个月的最后一天? - SQL

Question

假设我有一些示例数据table_name_a如下所示：

code     val_a   date
-------------------------
1   00001    500    20191101
2   00001    1000   20191130
3   00002    200    20191101
4   00002    400    20191130
5   00003    200    20191101
6   00003    600    20191130

20191101 和 20191130 之间有一些codeval_a，我想在每个code上获取当月的最后一天值，我的 SQL 查询如下(需要匹配Hive和Impla(：

SELECT code, max(date) AS date, val_a
FROM table_a
WHERE date BETWEEN '20090601'
AND '20090630'
GROUP BY code, val_a

但是上面的查询是错误的(代码的val_a不是当月的最后一天(，我的预期输出如下：

code    val_a      date
--------------------------
1   00001    1000   20191130
2   00002    400    20191130
3   00003    600    20191130

非常感谢您的任何建议。

Answer 1

我们可以在这里尝试使用ROW_NUMBER解决方案：

WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY code ORDER BY date DESC) rn
FROM table_a
-- WHERE date BETWEEN '20090601' AND '20090630'
-- your current WHERE clause is dubious
)
SELECT code, date, val_a
FROM cte
WHERE rn = 1;

请注意，将日期存储为文本不是最佳做法。 话虽如此，鉴于您以固定宽度的 ISO 格式存储日期，在这种情况下我们仍然可以使用这些日期。 另外，您当前的WHERE条款没有意义，所以我将其注释掉了。

Answer 2

您可以尝试以下代码。在子查询中，您可以获取最大日期以及代码。WHERE IN子句用作数据的筛选器。

SELECT code, val_a, date
FROM table_a
WHERE (code, date) IN 
(SELECT code, MAX(date) 
FROM table_a
GROUP BY code)

Answer 3

在更一般的方式中，您可以使用corrselect子查询：

select a.*
from table_a a
where a.date = (select max(a1.date) from table_a a1 where a1.code = a.code);

Answer 4

使用 row_number：

with your_data as (
select stack(6, 
'00001',500 ,'20191101',
'00001',1000,'20191130',
'00002',200 ,'20191101',
'00002',400 ,'20191130',
'00003',200 ,'20191101',
'00003',600 ,'20191130' ) as (code,val_a,date)
)
select  code,val_a,date
from
(  
select code,val_a,date, 
--partition by code and months, max date first
row_number() over(partition by code, substr(date, 1,6) order by date desc) rn        
from your_data d 
)s where rn=1   
;

结果：

OK
code    val_a   date
00001   1000    20191130
00002   400     20191130
00003   600     20191130
Time taken: 54.641 seconds, Fetched: 3 row(s)

Answer 5

如果您只需要该月最后一天的数据，则可以在WHERE子句中的日期上使用LAST_DAY和TRUNC函数，如下所示：

SELECT
CODE,
DATE AS "DATE", -- removed MAX
VAL_A
FROM
TABLE_A
WHERE
DATE BETWEEN '20090601' AND '20090630'
AND TRUNC(LAST_DAY(MAX(DATE))) = TRUNC(DATE); -- added this condition
-- removed the GROUP BY clause

干杯！！