如何查询每个城市最新创建日期的全部地理Shape数据？

原查询存在的问题 #

原查询使用ROW_NUMBER()会给同一城市同一日期的行分配唯一序号，且ANY_VALUE(shape)会随机选取单个shape值，导致仅返回单条数据，无法获取该日期下的全部shape记录。

解决方案一：使用窗口函数RANK()替代ROW_NUMBER() #

直接通过窗口函数标记最新日期的所有行，无需聚合操作：

SELECT city_id, city_name, shape, created_date AS date
FROM (
    SELECT 
        city_id, 
        city_name, 
        shape, 
        created_date,
        RANK() OVER (PARTITION BY city_id, city_name ORDER BY created_date DESC) AS rank
    FROM test
) t
WHERE rank = 1
ORDER BY created_date DESC;

RANK()会为同一城市同一日期的所有行分配相同的rank=1，筛选后即可得到该城市最新日期下的全部shape数据。

解决方案二：先获取最新日期再关联原表 #

通过子查询先确定每个城市的最新创建日期，再关联原表匹配所有对应记录：

SELECT t.city_id, t.city_name, t.shape, t.created_date AS date
FROM test t
INNER JOIN (
    SELECT city_id, city_name, MAX(created_date) AS latest_date
    FROM test
    GROUP BY city_id, city_name
) latest 
    ON t.city_id = latest.city_id 
    AND t.city_name = latest.city_name 
    AND t.created_date = latest.latest_date
ORDER BY t.created_date DESC;

这种方式逻辑直观，适合对窗口函数不太熟悉的场景。

内容的提问来源于stack exchange，提问作者BoB