sqlite Groupby和对同一列进行排序

0sgqnhkj  于 2023-04-06  发布在  SQLite
关注(0)|答案(2)|浏览(147)

如何在sqlite中对列进行排序和goupby。我正在尝试使用Python。

query = '''
  SELECT Agency, COUNT(*) AS NumComplaints
    FROM data
    GROUP BY UPPER(Agency)
    ORDER BY -Agency
    
'''
df = pd.read_sql_query(query, disk_engine)
df.tail(50)

ORDER BY在这里没有影响。我应该怎么做才能按降序对Agency列进行排序。
我试过使用Orderby Agency DESCORDER BY -Agency。这里的ORDER BY没有影响。我得到的输出是一样的。

j9per5c4

j9per5c41#

试着给一个别名指定一个顺序.

SELECT UPPER(Agency) AS agency_upper, COUNT(*) AS Num=Complaints
FROM data
GROUP BY agency_upper
ORDER BY agency_upper DESC

DEMO
如果不这样做,您将按每个组中的随机元素进行排序,而这些元素可能是大小写的混合,这会影响排序。

sdnqo3pr

sdnqo3pr2#

首先:你确定你正在尝试按Agency排序,而不是Count(否则你为什么要按DESC顺序排序?)
SQLite默认情况下使用不区分大小写的排序,所以我不相信在UPPER(Agency)Agency上分组会有什么不同。你可以在这里用这个查询来测试:

with data as (
  select "police" as agency union all
  select "Transportation" union all
  select "transportation"
)   
SELECT UPPER(Agency), COUNT(*) AS NumComplaints
FROM data
GROUP BY Agency
ORDER BY 2 DESC -- or change to "ORDER BY 1" to order on agency

您可以尝试使用查询here
此外,您还可以手动指定排序规则是按不区分大小写的字符串分组还是按区分大小写的字符串分组。例如:

-- ignore case: 'a' = 'A'
GROUP BY Agency COLLATE NOCASE

或者:

-- exact case: 'a' != 'A'
GROUP BY Agency COLLATE BINARY

https://www.sqlite.org/datatype3.html#:~:text=7.%20Collating%20Sequences

相关问题