我试图通过从两个不同的日期列中获取最大值来查询表,输出的是这两个日期中最大值的所有记录
该表有6列,其中包括st_id(字符串)(有多个条目具有相同的id)、as_of_dt(整数)和ld_dt_ts(时间戳)。从这个表中,我尝试获取as_of_dt和ld_dt_ts的最大值,并按st_id分组,并显示所有记录。
这工作得很好,但并不是最优的
SELECT A.st_id, A.fl_vw, A.tr_record FROM db.tablename A
INNER JOIN (
SELECT st_id, max(as_of_dt) AS as_of_dt, max(ld_dt_ts) AS ld_dt_ts
From db.tablename
group by st_id
) B on A.st_id = B.st_id and A.as_of_dt = B.as_of_dt and A.ld_dt_ts= B.ld_dt_ts
--
预期结果应返回同时具有最大as_of_dt和ld_dt_ts的st_id,即,这将是每个st_id的最新记录。
发布于 2019-06-19 08:38:16
使用解析rank()
函数。rank()
会将1
分配给st_id
分区中日期最大的所有记录:
SELECT s.st_id, s.fl_vw, s.tr_record
from
(
SELECT A.st_id, A.fl_vw, A.tr_record,
rank() over(partition by st_id order by as_of_dt desc) rnk_as_of_dt,
rank() over(partition by st_id order by ld_dt_ts desc) rnk_ld_dt_tsrnk
FROM db.tablename A
)s
WHERE rnk_as_of_dt=1 ANDrnk=1 rnk_ld_dt_ts=1 --get records with max dates in both columns
两个等级可以像这样组合:
SELECT s.st_id, s.fl_vw, s.tr_record
from
(
SELECT A.st_id, A.fl_vw, A.tr_record,
rank() over(partition by st_id order by as_of_dt desc, ld_dt_ts desc) rnk
FROM db.tablename A
)s
WHERE rnk=1 --get records with max dates combination
但这与原始查询并不完全相同。例如,如果您有以下数据集:
st_id, as_of_dt, ld_dt_ts
1 1 2
1 2 1
然后这个查询
SELECT st_id, max(as_of_dt) AS as_of_dt, max(ld_dt_ts) AS ld_dt_ts
From db.tablename
group by st_id
将返回:
st_id, as_of_dt, ld_dt_ts
1 2 2
final join不会返回任何行,因为不存在任何具有这种组合的行,而组合了两个ranks的查询将返回:
st_id, as_of_dt, ld_dt_ts
1 2 1
如果您的数据中不存在这样的数据集(例如,ld_dt_ts为always>=as_of_dt),那么您可以将等级合并为单个等级,甚至在order by中仅使用一个日期。
发布于 2019-06-20 00:24:18
--我使用的是row_number()函数,--
row_number() over()(PARTITION BY st_id ORDER BY as_of_dt DESC,ld_dt_ts DESC) RN from db.tablename s)tmp where RN=1
https://stackoverflow.com/questions/56657156
复制相似问题