SQLite | Group By 和 Order By 子句

最新推荐文章于 2024-02-01 19:28:15 发布
最新推荐文章于 2024-02-01 19:28:15 发布
阅读量1.3k 收藏 7
点赞数 2
分类专栏： # SQLite SQL 文章标签： sql 数据库 sqlite
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45488228/article/details/104436653
版权

文章目录

1. Group by and Order by

我们在上一篇中介绍了 Where 子句，接下来我们将使用 Group by 和 Order by 子句，对数据进行聚合和排序。

  • 使用Jupyter Notebook 运行 SQL 语句需安装 ipython-sql

  • %sql 以及 %%sql 为在 Notebook 中运行 SQL 语句，在 SQLite 命令行或 SQLite Stiduo 中不需要 %sql 或 %%sql

载入 SQL 以及连接 SQLite：

%load_ext sql
%sql sqlite:///DataBase/weather_stations.db

'Connected: @DataBase/weather_stations.db'

本文将使用 weather_stations.db 数据库，其中包含了 STATION_DATA 表。

首先查看 STATION_DATA 表中的数据：

%sql select * from station_data limit 0,5; -- 筛选前五行

 * sqlite:///DataBase/weather_stations.db
Done.
station_numberreport_codeyearmonthdaydew_pointstation_pressurevisibilitywind_speedtemperatureprecipitationsnow_depthfograinhailthundertornado
14308034DDA72002122133.8987.43.40.2360None11111
76644039537B199810172.71014.65.96.783.30None00000
176010C3C6D5200151855.7None7.34.369.10None00000
1256001451502007101433None6.92.539.70None00000
470160EF616A196772965.6None9.21.272.40.04None00000

1.1 Group Records

首先从最简单的聚合方法开始：计数：

%%sql
select count(*) as record_cound from station_data;

 * sqlite:///DataBase/weather_stations.db
Done.
record_cound
28000

count(*) 意味着计算记录的长度，你也可以和其他 SQL 操作符结合起来使用，比如 where，我们可以这样计算 tornado 出现的次数：

%%sql
select count(*) as record_count from station_data
where tornado == 1;

 * sqlite:///DataBase/weather_stations.db
Done.
record_count
3000

我们找到了 3000 条包含 tornado 的记录，但如果我们想要按年计数呢？我们可以这样写：

%%sql
select year, 
count(*) as record_count 
from station_data
where tornado == 1
group by year
limit 0,3; -- 只展示前三条

 * sqlite:///DataBase/weather_stations.db
Done.
yearrecord_count
19373
19413
19423

我们现在可以看到每年的计数，让我们拆分下这个查询来看看怎么执行的：

select year,              -- 1. 首先，我们选择了 year（select year）
count(*) as record_count  -- 2. 然后我们用 **count(\*)** 对筛选的记录进行了计数
from station_data
where tornado == 1        -- 3. 我们筛选了 tornado 为 true 的数据
group by year             -- 4. 最后，按年进行分类

我们也可以在多个 field 上进行聚合：

%%sql
select year, month,
count(*) as record_count
from station_data
where tornado == 1
group by year, month
limit 0,3;

 * sqlite:///DataBase/weather_stations.db
Done.
yearmonthrecord_count
193773
194183
1942103

此外，在使用 group by 时，我们可以也用 序数位置（ordinal positions）：

%%sql
select year, month,
count(*) as record_count
from station_data
where tornado == 1
group by 1, 2 -- ordinal positions
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.
yearmonthrecord_count
193773
194183
1942103
194313
194343

不是所有的平台都支持 ordinal positions，例如 Oracle 和 SQL Server，就只能写全称

1.2 Ordering Records

需要注意到，我们通过 group 得到的数据中 month 并不是按自然月份排序的，所以字哦好就是同时使用 oreder by 操作符来进行排序，如果你想要先按年份排序，再按月份排序，你只需要添加：

%%sql
select year, month,
count(*) as record_count
from station_data
where tornado == 1
group by 1, 2 -- ordinal positions
order by 1, 2 -- order by 同样支持 ordinal positions
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.
yearmonthrecord_count
193773
194183
1942103
194313
194343

order by 默认是按升序（ASC）排列的，然而你可能更对近期的数据感兴趣，你可以通过添加 DESC 来指定排序方式：

%%sql
select year, month,
count(*) as record_count
from station_data
where tornado == 1
group by year, month
order by year DESC, month
limit 0,5;

 * sqlite:///DataBase/weather_stations.db
Done.
yearmonthrecord_count
201036
200913
200923
200942
200956

1.3 Aggregate Functions

我们已经使用 count(*) 来对记录进行计数了，但还有其他的一些聚合函数（AggregateyFunctions），
如 sum()、min()、max() 和 avg()。我们可以在特定的列上使用聚合函数来进行计算。

图1 SQLite 内置聚合函数

但首先让我们来看看 count() 的另一种使用方式， count() 可以用于除了计数以外的其他用途。如果你不使用 * ，
而是指定某一列，那么它将会计算所有非缺失值（non-null）的个数。举个例子，我们可以计算 snow_depth 中非缺失值的个数：

%%sql
select count(snow_depth) as recorded_snow_depth_count
from station_data

 * sqlite:///DataBase/weather_stations.db
Done.
recorded_snow_depth_count
1552

让我们进一步看看聚合函数，如果你想要看看你从 2000 年开始每个月的平均温度，你可以先筛选 2000 年的记录，
然后按月份分组，最后计算平均温度：

%%sql
select month, avg(temperature) as avg_temp
from station_data
where year >= 2000
group by month
limit 0,3;

 * sqlite:///DataBase/weather_stations.db
Done.
monthavg_temp
141.55585443037976
238.98063127690104
348.975062656641576

sum() 是另一个常见的聚合操作符，为了得到 2000 年至今每年的下雪深度，你可以这样查询：

%%sql
select year, sum(snow_depth) as total_snow
from station_data
where year >= 2000
group by year
limit 0,3;

 * sqlite:///DataBase/weather_stations.db
Done.
yeartotal_snow
2000685.8999999999999
2001391.90000000000003
2002437.69999999999993

你可以在一次查询中多次使用聚合操作，我们将 2000 年以来的下雪总量、下雨总量和最大降雨量分别统计出来，并保留两位小数：

%%sql
select year,
round(sum(snow_depth), 2) as total_snow,
round(sum(precipitation), 2) as total_precipitation,
round(max(precipitation), 2) as max_precipitation
from station_data
where year >= 2000
group by year
limit 0,3;

 * sqlite:///DataBase/weather_stations.db
Done.
yeartotal_snowtotal_precipitationmax_precipitation
2000685.927.570.87
2001391.938.152.95
2002437.743.065.0

1.4 The Having Statement

假设你想要基于一个聚合值来筛选记录，你的第一反应应该是使用 where 子句。确实， where 子句可以
用来筛选记录，但是却无法用于聚合值上。举个例子，如果你想使用 where 子句筛选出总下雨量大于 30 的记录，
就会出现以下错误：

%%sql
select year, sum(precipitation) as total_precipitation
from station_data
where total_precipitation > 30
group by year
limit 0,3;

 * sqlite:///DataBase/weather_stations.db
(sqlite3.OperationalError) misuse of aggregate: sum()
[SQL: select year, sum(precipitation) as total_precipitation
from station_data
where total_precipitation > 30
group by year
limit 0,3;]
(Background on this error at: http://sqlalche.me/e/e3q8)

为什么不起作用呢？首先我们来看下聚合的原理，首先程序一行一行的扫描，找出那些在 where 子句
上成立的数据，然后再进行聚合。然而在聚合前并没有 total_precipitation 这一列数据，因此出错。

当你想在聚合值上执行 where 这个方法时，只能使用 having 这个关键词：

%%sql
select year,
sum(precipitation) as total_precipitation
from station_data
group by year
having total_precipitation > 30
limit 0,3

 * sqlite:///DataBase/weather_stations.db
Done.
yeartotal_precipitation
197335.07999999999996
197442.209999999999994
197548.25999999999997

having 相当于聚合版的 where，但并不是所有平台都支持在 aliases 上使用 having ，
如 Oracle（group by 也不行），这意味着当你使用 having 时需要再输入一次聚合函数，像这样：

%%sql
select year,
sum(precipitation) as total_preicipitation
from station_data
group by year
having sum(precipitation) > 30
limit 0,3

 * sqlite:///DataBase/weather_stations.db
Done.
yeartotal_preicipitation
197335.07999999999996
197442.209999999999994
197548.25999999999997

1.5 Getting Distinct Records

当我们使用 **select from** 时，记录中可能会包含重复值，如果你只想要返回**唯一值（distinct records）**，
你可以使用 **select distinct from**，比如我们的 station_data，表中 station_number 一列包含
了 28000 个值，但你通过 **select distinct from** 后会发现其中是 6368 个值不断重复出现组成的

%%sql
select count(station_number) as duplicate_num
from station_data;

 * sqlite:///DataBase/weather_stations.db
Done.
duplicate_num
28000
%%sql
select count(distinct station_number) as distinct_num
from station_data;

 * sqlite:///DataBase/weather_stations.db
Done.
distinct_num
6368

参考资料

[1] Thomas Nield.Getting Started with SQL[M].US: O’Reilly, 2016: 29-37

相关文章：

SQL | 目录
SQLite | SQLite 与 Pandas 比较篇之一
SQLite | Select 语句
SQLite | Where 子句
SQLite | CASE 子句
SQLite | Join 语句
SQLite | 数据库设计与 Creat Table 语句
SQLite | Insert、Delete、Updata 与 Drop 语句

X1AO___X1A
关注
专栏目录
SQLite学习（六）SQLite的GLOB、LIMIT、ORDER、GROUP UP子句语法和应用
Designer 小郑的技术博客
04-22 3345
本文讲解SQLite中GLOB、LIMIT、ORDER、GROUP UP子句的用法，用最简单的方法做最通俗的教学！
SQLite数据库—group by 语句
niuruijun的博客
06-07 4432
GROUP BY对数据进行分组SQLite的GROUP BY 子句用于与SELECT语句一起使用，来对相同的数据进行分组。在SELECT语句中，GROUP BY子句放在WHERE子句之后，放在ORDER BY子句之前。 用法：select 列名,列名 from 表名 group by 列名 order by 列名 例1：对CPU型号进行分组，并查询使用相同CPU的手机数（对CPU型号相同的信息合并...
实验七SQLite和SQLiteDatabase的使用.docx
12-14
实验七SQLite和SQLiteDatabase的使用.docx
13-SQLite之group by
每天积累一点点
05-25 5689
一、group by 概述 “group by”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。 group by经常和聚合函数（avg、count、max、min、sum）一起使用 二、原始表 三、案例分析 1.查看分别在bj、sz有多少
mysql group by菜鸟教程_SQLite Group By | 菜鸟教程
weixin_39903846的博客
02-03 86
SQLite Group BySQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。语法下面给出了 GROUP BY 子句的基本语法。GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。SELEC...
SQLite的group by
Super_Jadroid的博客
01-22 957
感觉很神奇，select * from  ShortMessages  group by Number 也能查出需要的结果，不会报错 先放上自己的模拟数据 然后结果就是这样-->>  得到的是按number分组后每个分组中的第一条数据，也就是最后添加的数据。 先给自己留个记忆。未完待续。。。
SQLite Group By
android开发笔记
04-13 899
SQLite 的GROUP BY子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。 在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。 语法 下面给出了 GROUP BY 子句的基本语法。GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。 SELECT colu...
SQLite | SQLite 与 Pandas 比较篇之一
侠的博客
02-22 2962
相关文章： SQL | 目录 SQLite | Select SQLite | Where SQLite | Group by and Order by 1. SQLite 与 Pandas 异同点比较 1.1 数据导入 1.1.1 SQLIte SQLite 需要首先导入数据库文件并使用 select 语句选取记录： 使用Jupyter Notebook 运行 SQL 语句需安装 ipyth...
SQLite Unions 子句
12-16
5. **ORDER BY与GROUP BY**：在UNION后的结果集上直接使用ORDER BY进行排序可能不会按预期工作，因为SQLite会先执行UNION，然后才排序。同样，GROUP BY通常用于聚合函数，如COUNT、SUM等，它不能直接应用在UNION的...
SQLite学习笔记五:Order By，Group By，Having，Distinct
了凡
06-18 6708
SQLite Order By SQLite的ORDER BY 子句是用来基于一个或多个列
SQLite – GROUP BY
dh7995的博客
01-02 111
SQLite - GROUP BY SQLite GROUP BY子句中使用与SELECT语句的合作安排相同的数据组。 在GROUP BY子句之前一个SELECT语句的WHERE子句,先于ORDER BY子句。 语法： GROUP BY子句的基本语法如下所示。 GROUP BY子句必须遵循的条件在WHERE子句中,必须先于ORDER BY子句使用的是如果一个人...
数据库SQL之实战distinct和group by
weixin_43868107的博客
10-11 516
题目描述 查找入职员工时间排名倒数第三的员工所有信息 CREATE TABLE employees ( emp_no int(11) NOT NULL, birth_date date NOT NULL, first_name varchar(14) NOT NULL, last_name varchar(16) NOT NULL, gender char(1) NOT NULL, hire_da...
SQLite数据库—select—group、distinct、limit、offset用法
niuruijun的博客
06-06 3264
1.GROUP BY对数据进行分组SQLite的GROUP BY 子句用于与SELECT语句一起使用，来对相同的数据进行分组。在SELECT语句中，GROUP BY子句放在WHERE子句之后，放在ORDER BY子句之前。 用法：select 列名,列名 from 表名 group by 列名 order by 列名 例1：对CPU型号进行分组，并查询使用相同CPU的手机数（对CPU型号相同的信息...
golang的sqlite驱动不使用cgo实现 更换gorm默认的SQLite驱动
最新发布
yinjl123456的博客
02-01 3363
启用CGO后，编译的是动态链库的二进制文件，那么你一旦更换平台，比如将程序放到Windows下运行，可能因为缺少相关的一些动态链库（比如：SQLite）从而无法运行，为了保持编译的二进制文件能够无动态依赖的情况下正常运行，因此不得不考虑静态编译，所以就和GORM SQLite驱动产生了冲突。很多瘦小设备，弱设备里的系统，没有相关依赖库和驱动，也很难安装相关的驱动，此时不得不考虑使用不依赖任何三方的驱动情况下运行程序。这样就可以在不依赖CGO的情况下编译出静态二进制文件，从而解决跨平台依赖动态链库的问题。
mysql aggregate_MySQL-滥用聚合函数(MySQL- Misuse of aggregate function)
weixin_31956641的博客
02-17 2338
MySQL-滥用聚合函数(MySQL- Misuse of aggregate function)我一直在尝试运行以下命令：select s.name, s.nrfrom sub sgroup by s.namehaving (select count(s.name) from sub s group by s.name) >10 * (select avg(count(s.name)) f...
SQLite入门到精通之九：排序和分组（Order By|Group By|Having|Distinct）
caobaokang的专栏
03-19 5293
SQLiteOrder By SQLite 的ORDER BY子句是用来基于一个或多个列按升序或降序顺序排列数据。 语法 ORDER BY 子句的基本语法如下： SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; ...
sqlite3学习之Limit 子句&ORDER BY 子句& GROUP BY 子句
luyaran的博客
01-11 4678
SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量，我们来看下带有 LIMIT 子句的 SELECT 语句的基本语法： SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] 再来看下 LIMIT 子句与 OFFSET 子句一起使用时的语法： SELECT column1, co...
【SQLITE】聚合函数的一些特征
Mercy92的博客
07-17 576
sqlite聚合的一些特点
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值