英文:
SQLite equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
问题
以下是翻译好的部分：
在Oracle SQL中有一种技术可以用于简化聚合查询：
在特定列上进行聚合，但从不同列获取信息，使用SELECT列表中的简单计算列。
如上所示，以下列可以获取城市名称，即使城市名称不在GROUP BY中：
any_value(city) keep (dense_rank first order by population desc)
有多种方法可以使用SQL来实现这种功能。我正在寻找SQLite中的解决方案，让我在一个计算列中执行它 - 在单个SELECT查询内（无子查询、连接、WITH等）。
问题：SQLite中是否有与Oracle的ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)等效的功能？
相关链接：
- YouTube：KEEP子句将使您的SQL查询简单（Oracle）
- db-oriented.com：ANY_VALUE和FIRST/LAST（KEEP）
- Stack Overflow：解释Oracle中FIRST/LAST的KEEP
编辑：
我将MAX()更改为ANY_VALUE()，因为我认为ANY_VALUE()更容易阅读。
可以通过在order by中添加, city desc来打破平局，使其具有确定性：
any_value(city) keep (dense_rank first order by population desc, city desc)
英文:
There's a technique in Oracle SQL that can be used to simplify aggregation queries:
Aggregate on a particular column, but get information from a different column, using a simple calculated column in the SELECT list.
--Oracle
--For each country, what city has the highest population? (where the country has more than one city)
--Include the city name as a column.
select
country,
count(*),
max(population),
any_value(city) keep (dense_rank first order by population desc) --<<--
from
cities
group by
country
having
count(*) > 1
As shown above, the following column can bring in the city name, even though the city name isn't in the GROUP BY:
any_value(city) keep (dense_rank first order by population desc)
There are a number of ways to achieve that kind of thing using SQL. I'm looking for a solution in SQLite that lets me do it in a calculated column -- all within a single SELECT query (no subqueries, joins, WITH, etc.).
Question: Is there equivalent functionality to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...) in SQLite?
Related:
- YouTube: The KEEP clause will KEEP your SQL queries SIMPLE (Oracle)
- db-oriented.com: ANY_VALUE and FIRST/LAST (KEEP)
- Stack Overflow: Explanation of KEEP in Oracle FIRST/LAST
Edit:
I changed MAX() to ANY_VALUE(), since I think ANY_VALUE() is easier to read.
Ties can be broken by adding , city desc to the order by, making it deterministic:
any_value(city) keep (dense_rank first order by population desc, city desc)
答案1
得分: 2
SQLite在聚合查询中支持裸列，如果使用了MAX()或MIN()中的一个。<br/>
例如，您的Oracle查询可以简单地写成：
select
country,
count(*),
max(population),
city -- this is valid and returns the city with the max population
from
cities
group by
country
having
count(*) > 1
查看演示。<br/>
英文:
SQLite supports bare columns in an aggregate query if one of MAX() or MIN() is used.<br/>
For example, your Oracle query can be written simply like:
select
country,
count(*),
max(population),
city -- this is valid and returns the city with the max population
from
cities
group by
country
having
count(*) > 1
See the demo.<br/>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论