![中国-制造业采购经理人指数](https://s2.loli.net/2024/12/24/SVL5Gv6NuxePjTX.png)



1. [**LPR**](https://sc.macromicro.me/charts/13442/cn-loan-prime-rate)

指贷款市场报价利率，和房贷利率有关。

2. [**国债收益率持续下跌意味着什么？**](https://www.huxiu.com/article/3822828.html)

普通投资人在资产配置方面该如何应对？

- 分红稳定、股息率高的优质蓝筹股
- 高成长性的科技公司
- 收益较高的公司债
- 贷款利率如进一步走低，购房会重新成为一种具有吸引力的投资方式
- 黄金和其他贵金属在低利率和经济不确定性较高的情况下，作为避险资产也值得配置

3. [**美元潮汐效应是如何影响全球的？**](https://finance.sina.com.cn/money/fund/jjzl/2024-08-26/doc-inckyrfc4756803.shtml)

![](https://s2.loli.net/2024/12/24/sciYNAt5fKVyeMz.png)

4. [**鹰派降息？3分钟搞懂美联储咋降的息！**](https://mp.weixin.qq.com/s/OIUnNktucLpYIuaN0YGTUQ)

调整利率其实是在控制流动性。

5. [**第三产业？**](https://www.gov.cn/gongbao/content/2003/content_62360.htm)

概括来说是指服务业。

6. [**养老院生意不好做，新注册量五年下降七成**](https://www.huxiu.com/article/3794812.html)

想象中养老是个朝阳产业，从这篇报道来看养老院目前还没有形成很好的盈利模式，但是投资人都比较看好未来。

7. [**药品集中招标采购**](https://baike.baidu.com/item/%E8%8D%AF%E5%93%81%E9%9B%86%E4%B8%AD%E6%8B%9B%E6%A0%87%E9%87%87%E8%B4%AD/4690493)

集采时期相关药企股价会下跌啊。

8. [**【南方网】想要孩子“蹿个子”？生长激素靠谱吗？**](https://mpa.gd.gov.cn/xwdt/xfjs/yxaq/content/post_4440800.html)

近期亲戚家有小孩子打这个生长激素，我也是第一次听说有这个东西。

9. [**怎么看待“通缩品”生意**](https://mp.weixin.qq.com/s/AuUmbT0TZkEjGofr9r7xYw)

一直在白嫖 ChatGPT 的免费额度，非常期待文中所说的“AI：现阶段看AI是典型的提升效率，没有创造新的终端体验，也一定是通缩的，甚至无法收费”场景成为现实。

10. [**伊利蒙牛未见底 | 牛熊榜**](https://pro.huxiu.com/article/3739462.html)

平时一直买伊利的奶喝。之前一直持有伊利股票，不温不火，直到最近上涨了一些就清仓了。从“液态奶还不是居民的生活必需品“来看，未来奶制品还有很大发展空间，静待下一个好价格。

## 参考

- [按揭：人生最大一笔贷款该怎么操作](https://blog.chives.life/post/financial-knowledge#%E6%8C%89%E6%8F%AD%EF%BC%9A%E4%BA%BA%E7%94%9F%E6%9C%80%E5%A4%A7%E4%B8%80%E7%AC%94%E8%B4%B7%E6%AC%BE%E8%AF%A5%E6%80%8E%E4%B9%88%E6%93%8D%E4%BD%9C)
- [生长激素](https://zh.wikipedia.org/wiki/%E7%94%9F%E9%95%BF%E6%BF%80%E7%B4%A0)

Chives Daily 2

![中国-居民消费价格](https://s2.loli.net/2024/12/24/UuLgYzTblHAV7nc.png)



1. [**如何判断一只股票的价格是否合理？李永乐老师讲『贴现法』对股票估值**](https://www.youtube.com/watch?v=VhunpYlc2Eo)

假设 r 是贴现率，那么投资 x 元，未来能获得`x(1+r)^n`。

- 贴现率会受以下因素影响
	- 无风险利率
	- 风险补偿
	- 通货膨胀
	- 其他

> 不同估值方法得出的结果会相差非常大。

- 股利贴现法估值，简单算法`股票价值 = 每股分红 / 贴现率`。
- 自由现金流贴现，简单算法`股票价值 = 每股现金流 / 贴现率`。

2. [**茅台股票为什么这么贵？如何用市净率、市盈率衡量股票价值？**](https://www.youtube.com/watch?v=3TgmqH7YbcY)

选股票如何避免踩雷？关键看三个指标：

- 市净率，越低股票资产越值钱。
- 市盈率，越低表示盈利性能好。
- 市盈增长比，越低表示成长性和价格都比较合适。

3. [**看懂了这个，你再去炒股；股市暴跌，为啥散户炒股票总赔钱？李永乐老师用数学告诉你！**](https://www.youtube.com/watch?v=g-wCpEZBEdw)

这集李老师通过数学证明了，存在一种概率策略能够使得散户总是亏钱而庄家稳赚。最好不要碰题材股，因为那里有很多庄家，请买基本面良好有发展前景的股票！

4. [**输了就加倍下注，能赚钱吗？股票下跌就补仓？李永乐老师讲赌徒谬误**](https://www.youtube.com/watch?v=yoOocF47BRI)

如果某支股票连续跌了好几天，那么下一个交易日有很大概率上涨？其实并没有，除非这个股票在100万个交易日连续下跌。股票下跌就加倍补仓只会加速耗光筹码，亏得更多。

5. [**如何理解股市指数，点数?**](https://www.zhihu.com/question/20985017/answer/799733119)

对于股市指数（如上证指数、深证成指等），“一个点”表示指数的单位变化。例如：上证指数从3000点涨到3001点，就是涨了一个点。另外，不同上下文中的“点”可能含义不同，比如[基点](https://wiki.mbalib.com/wiki/%E5%9F%BA%E7%82%B9)。

6. [**赤字**](https://zh.wikipedia.org/wiki/%E8%B5%A4%E5%AD%97)

入不敷出。

7. [**中美贸易额近6年数据，贸易战之后不降反升的原因分析**](https://xueqiu.com/7659667127/311378316)

- 中国产品性价比高。
- 贸易战使贸易关系/供应链重新构建。
- 全球价值链复杂，贸易战影响力下降。

8. [**行业探讨｜特朗普上台，这些赛道值得关注**](https://xueqiu.com/3199838879/311706016)

- 内需相关赛道：消费、地产、家电、医药、机械等。
- 自主相关赛道：半导体、信创等。
- 全球化突破：汽车、新能源等。

9. [**机遇与挑战——特朗普胜选带给A股的诸多影响**](https://xueqiu.com/7789206927/311415726)

![](https://s2.loli.net/2024/12/22/1sFbiKyDXAtrOgH.png)

10. [**商品房按套内建筑面积计价，是取消公摊了吗？**](https://www.cls.cn/detail/851167)

并没有。房屋总价不会发生变化，所以面积单价会上涨。

- 套内建筑面积=套内使用面积+套内墙体面积+阳台建筑面积
- 公用建筑面积
	- 电梯井、楼梯间、垃圾道、变电室、设备间、公共门厅和过道、地下室、值班警卫室以及其他为整栋建筑服务的公共用房和管理用房建筑面积。
	- 单元与公用建筑空间之间的分隔墙以及外墙。

## 参考

- [财政部-中国国债收益率曲线](https://yield.chinabond.com.cn/cbweb-czb-web/czb/moreInfo?locale=cn_ZH&nameType=1)
- [中国 - 通货膨胀率](https://zh.tradingeconomics.com/china/inflation-cpi)

Chives Daily 1

窗口函数和聚合函数的区别在于，窗口函数为每一行记录都返回一个结果，而聚合函数为每个分组返回一个结果。

![图10.1 聚合函数与窗口函数的区别](https://s2.loli.net/2024/12/13/pLKV3tgN915byID.png)



## 定义

> 在某些数据库中，窗口函数也被称为在线分析处理（OLAP）函数，或者分析函数（Analytic Function）​。

```sql
win_func_name ([expression]) OVER (
	PARTITION BY ... -- 类似 GROUP BY
	ORDER BY ...
	frame_clause -- 窗口大小
)

frame_clause:
{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end
```

窗口函数 OVER 子句中的 frame_clause 选项用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。

- ROWS 表示以数据行为单位计算窗口的偏移量。
- RANGE 表示以数值（例如10天、5km等）为单位计算窗口的偏移量。
- frame_start 选项用于定义窗口的起始位置
	- UNBOUNDED PRECEDING 表示窗口从分区的第一行开始。
	- N PRECEDING 表示窗口从当前行之前的第 N 行开始。
	- CURRENT ROW 表示窗口从当前行开始。
- frame_end 选项用于定义窗口的结束位置
	- CURRENT ROW 表示窗口到当前行结束。
	- M FOLLOWING 表示窗口到当前行之后的第 M 行结束。
	- UNBOUNDED FOLLOWING 表示窗口到分区的最后一行结束。

> 理解“当前行”吗？在文章开头说过，窗口函数为每一行记录生成一个结果，“当前行”就表示窗口函数在为哪一行生成结果。

窗口大小选项举例

```sql
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```

上面的语句用下图表示

![图10.2 窗口大小选项](https://s2.loli.net/2024/12/13/c54J2dFamxWTbNH.png)

简单使用例子

```sql
-- 不同部门分别统计员工的月薪合计
SELECT emp_name "员工姓名", salary "月薪", dept_id "部门编号",
	SUM(salary) OVER (
		PARTITION BY dept_id
	) AS "部门合计"
FROM employee;

-- 员工在部门内的月薪排名
SELECT emp_name "姓名", salary "月薪", dept_id "部门编号",
	RANK() OVER ( -- 窗口内的数据排序后，RANK 返回从1-n的整数 
		PARTITION BY dept_id
		ORDER BY salary DESC
	) AS "部门排名"
FROM employee;
```

- 对于 oracle、pg 以及 sqlite，窗口函数中的 ORDER BY 也可以使用 NULLS FIRST 或者 NULLS LAST 选项指定空值的排序位置。

## 聚合窗口函数 Aggregate Window Function

包括 AVG()、SUM()、COUNT()、MAX() 以及 MIN() 等函数。

### 移动平均值

```sql
-- 获取不同产品每个月销售额、最近3个月平均销售额
SELECT product AS "产品", ym "年月", amount "销售额",
	AVG(amount) OVER (
		PARTITION BY product
		ORDER BY ym
		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 从当前行的前2行开始，到当前行结束
	) AS "最近平均销售额"
FROM sales_monthly
ORDER BY product, ym;
```

![](https://s2.loli.net/2024/12/16/ofJH5MYpikrN6Q2.png)

### 累计求和

```sql
-- 不同产品截至当前月份的累计销售额
SELECT product AS "产品", ym "年月", amount "销售额",
	SUM(amount) OVER (
		PARTITION BY product
		ORDER BY ym
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 从当前分区第一行开始，到当前行结束
	) AS "累计销售额"
FROM sales_monthly
ORDER BY product, ym;
```

![](https://s2.loli.net/2024/12/16/JoN7FWSqOgu8QaZ.png)

- 对于聚合窗口函数，如果没有指定 ORDER BY 选项，默认的窗口大小就是整个分区。如果指定了 ORDER BY 选项，默认的窗口大小就是分区的第一行到当前行。

```sql
-- 查找短期之内（5天）累计转账超过100万元的账户
-- oracle mysql pg
SELECT log_ts, from_user, total_amount
FROM (
	SELECT log_ts, from_user,
		SUM(amount) OVER (
			PARTITION BY from_user
			ORDER BY log_ts
			RANGE INTERVAL '5' DAY PRECEDING -- 根据排序的 log_ts，从前5天开始，到当前结束
		) AS total_amount
	FROM transfer_log
	WHERE TYPE = '转账'
) t
WHERE total_amount >= 1000000;

-- sqlite 不支持 INTERVAL，但是可以将时间转为整数
WITH t1(log_ts, unix, from_user, amount) AS (
	SELECT log_ts, CAST(STRFTIME('%s', log_ts) AS INT), from_user, amount
	FROM transfer_log
	WHERE type = '转账'
)
SELECT log_ts, from_user, total_amount
FROM (
	SELECT log_ts, from_user,
		SUM(amount) OVER (
			PARTITION BY from_user
			ORDER BY unix
			RANGE 5 * 86400 PRECEDING
		) AS total_amount
	FROM t1
) t
WHERE total_amount >= 1000000;
```

- mss 中的 RANGE 窗口大小选项只能指定 UNBOUNDED PRECEDING、UNBOUNDED FOLLOWING 或者 CURRENT ROW。

## 排名窗口函数 Ranking Window Function

包括 ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST() 以及 NTILE() 等函数。

- ROW_NUMBER 函数可以为分区中的每行数据分配一个序列号，序列号从1开始。
- RANK 函数返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
- DENSE_RANK 函数返回当前行在分区中的名次。即使存在名次相同的数据，后续的排名也是连续值。
- PERCENT_RANK 函数以百分比的形式返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
- CUME_DIST 函数计算当前行在分区内的累积分布。
- NTILE 函数将分区内的数据分为N等份，并返回当前行所在的分片位置。
- 排名窗口函数不支持动态的窗口大小选项，而是以整个分区作为分析的窗口。

### 排名函数比较

```sql
SELECT d.dept_name AS "部门名称", e.emp_name AS "姓名", e.salary AS "月薪",
	ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "row_number",
	RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "rank",
	DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "dense_rank",
	PERCENT_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);
```

![](https://s2.loli.net/2024/12/16/dIsn41TY36hLHuG.png)

- 可以使用 COUNT() 窗口函数产生和ROW_NUMBER函数相同的结果

更简单的写法

- oracle mss 不支持简写

```sql
-- mysql pg sqlite
SELECT d.dept_name AS "部门名称", e.emp_name AS "姓名", e.salary AS "月薪",
	ROW_NUMBER() OVER w AS "row_number",
	RANK() OVER w AS "rank",
	DENSE_RANK() OVER w AS "dense_rank",
	PERCENT_RANK() OVER w AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
WINDOW w AS (PARTITION BY e.dept_id ORDER BY e.salary DESC);
```

```sql
-- 查找每个部门中最早入职的2名员工
WITH ranked_emp AS (
	SELECT d.dept_name, e.emp_name, e.hire_date,
		ROW_NUMBER() OVER (PATRITION BY e.dept_id ORDER BY e.hire_date) AS rn
	FROM employee e
	JOIN department d ON (e.dept_id = d.dept_id)
)
SELECT dept_name "部门名称", emp_name "姓名", hire_date "入职日期", rn "入职顺序"
FROM ranked_emp
WHERE rn <= 2;
```

![](https://s2.loli.net/2024/12/16/c5yxvEWs1bkFRPN.png)

### 累计分布

CUME_DIST 返回排名在当前行之前（包含当前行）所有数据所占的比率。

```sql
SELECT emp_name AS "姓名", salary AS "月薪",
	CUME_DIST() OVER (ORDER BY salary) AS "累计占比"
FROM employee;
```

![](https://s2.loli.net/2024/12/16/7pJmVUqw9gEdHTN.png)

- 结果显示8%的员工月薪小于或等于4000元

```sql
-- 将员工按照入职先后顺序分为5组，并计算每个员工所在的分组
SELECT emp_name AS "姓名", hire_date AS "入职日期",
	NTILE(5) OVER (ORDER BY hire_date) AS "分组位置"
FROM employee;
```

![](https://s2.loli.net/2024/12/16/QRxcjMaYE5k2dFA.png)

## 取值窗口函数 Value Window Function

用于返回指定位置上的数据行，包括 FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()、NTH_VALUE() 等函数。

- LAG 函数可以返回窗口内当前行之前的第 N 行数据。
- LEAD 函数可以返回窗口内当前行之后的第 N 行数据。
- FIRST_VALUE 函数可以返回窗口内第一行数据。
- LAST_VALUE 函数可以返回窗口内最后一行数据。
- NTH_VALUE 函数可以返回窗口内第 N 行数据。
- 其中，LAG 函数和 LEAD 函数不支持动态的窗口大小，它们以整个分区作为分析的窗口。

### 环比

指的是本期数据与上期数据相比的增长，`(后一期数据 - 前一期数据) / 前一个数据 * 100%`。

```sql
-- 各种产品每个月的环比增长率
SELECT product AS "产品", ym "年月", amount "销售额",
	(
		(amount - LAG(amount, 1) /*上一期销售额*/ OVER (PARTITION BY product ORDER BY ym)) / LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym)
	) * 100 AS "环比增长率（%）"
FROM sales_monthly
ORDER BY product, ym;
```

![](https://s2.loli.net/2024/12/16/GjHWmbYDpw5CKiZ.png)

### 同比

指的是本期数据与上一年度或历史同期相比的增长。

```sql
-- 各种产品每个月的同比增长率
SELECT product AS "产品", ym "年月", amount "销售额",
	(
		(amount - LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)) / LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)
	) * 100 AS "同比增长率（%）"
FROM sales_monthly
ORDER BY product, ym;
```

![](https://s2.loli.net/2024/12/16/89DrI2JS7qRihV1.png)

### 复合增长率

直接上公式

$$
复合增长率 = \left( (\frac{第n期数据}{第1期数据})^{\frac{1}{n-1}} - 1 \right) \times 100
$$

```sql
WITH s (product, ym, amount, first_amount, num) AS (
	SELECT product, ym, amount,
		FIRST_VALUE(amount) OVER (PARTITION BY product ORDER BY ym),
		ROW_NUMBER() OVER (PATRITION BY product ORDER BY ym)
	FROM sales_monthly
)
SELECT product AS "产品", ym "年月", amount "销售额",
	(
		POWER(1.0*amount/first_amount, 1.0/NULLIF(num-1,0)) - 1
	) * 100 AS "月均复合增长率（%）"
FROM s
ORDER BY product, ym;
```

![](https://s2.loli.net/2024/12/16/7fak1Ixpcirz3Yu.png)

```sql
-- 不同产品最低销售额、最高销售额以及第三高销售额所在的月份
SELECT product AS "产品", ym "年月", amount "销售额",
	FIRST_VALUE(ym) OVER (
		PARTITION BY product ORDER BY amount DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS "最高销售额月份",
	LAST_VALUE(ym) OVER (
		PARTITION BY product ORDER BY amount DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS "最低销售额月份",
	-- mss 不支持 NTH_VALUE
	NTH_VALUE(ym, 3) OVER (
		PARTITION BY product ORDER BY amount DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS "第三高月份"
FROM sales_monthly
ORDER BY product, ym;
```

![](https://s2.loli.net/2024/12/16/UX783fgMGkOFVjE.png)

- FIRST_VALUE、LAST_VALUE、NTH_VALUE 的默认窗口都是从分区的第一行到当前行。

窗口函数

通用表表达式（Common Table Expression）能够简化复杂的子查询和连接查询，实现树状结构数据的遍历，提高 SQL 语句的可读性和性能。



## 表即变量

```sql
WITH cte_name(col1, col2, ...) AS (
	subquery
)
SELECT * FROM cte_name;
```

- cte_name: CTE 的名称，后面是可选的字段名。
- AS 后的 subquery 用于定义 CTE。
- SELECT 是主查询，可以引用所定义的 CTE。还可以使用 INSERT、UPDATE、DELETE 等。

```sql
WITH t(n) AS (
	SELECT 1 -- oracle 需要使用 SELECT 1 FROM dual
)
SELECT n
FROM t;
```

> WITH 语句定义了一个变量，这个变量的值是一个表，所以称为通用表表达式。CTE 和临时表或子查询类似，可以用于 SELECT、INSERT、UPDATE 以及 DELETE 等语句。

```sql
-- 定义多个 CTE
WITH t1(n) AS (
	SELECT 1 -- oracle 需要使用 SELECT 1 FROM dual
),
t2(m) AS (
	SELECT n+1
	FROM t1 -- 可以引用前面的 CTE
)
SELECT t1.n, t2.m
FROM t1
CROSS JOIN t2;
```

## 强大的递归

```sql
WITH RECURSIVE cte_name AS (
	cte_query_initial -- 初始化部分
	UNION [ALL] -- 合并
	cte_query_iterative -- 递归部分，可以对当前 CTE 进行自我引用
) SELECT * FROM cte_name;
```

- 每一次递归查询语句执行的结果都会再次作为输入，传递给下一次查询。
- 如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归。
- oracle 和 mss 不支持 RECURSIVE，而直接使用 WITH 定义递归形式的CTE，同时它们必须使用 UNION ALL 运算符。
- sqlite 可以省略 RECURSIVE。

一些使用示例。

```sql
-- 递归生成1～10
-- mysql pg sqlite
WITH RECURSIVE t(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;

-- oracle
WITH t(n) AS
(
	SELECT 1 FROM dual
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;

-- mss
WITH t(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;
```

1. 首先运行初始化语句，生成数字1。
2. 第1次运行递归部分，此时n等于1，生成数字2（n+1）​。
3. 第2次运行递归部分，此时n等于2，生成数字3。
4. 继续运行递归部分，直到n等于9，生成数字10。
5. 第10次运行递归部分，此时n等于10。由于不满足查询条件（WHERE n < 10）​，不返回任何结果，同时终止递归。
6. 最后，主查询语句返回t中的全部数据，也就是一个1～10的数字序列。

```sql
-- 遍历层次/树结构，自顶向下
-- mysql
WITH RECURSIVE employee_path (emp_id, emp_name, path) AS
(
	-- 初始部分
	SELECT emp_id, emp_name, emp_name AS path
	FROM employee
	WHERE manager IS NULL -- 老板，是树的根
	UNION ALL
	-- 递归部分
	SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name)
	FROM employee_path ep
	JOIN employee e ON ep.emp_id = e.manager -- 当前层关联下一层
)
SELECT emp_name AS "员工姓名", path AS "管理路径"
FROM employee_path
ORDER BY emp_id;
```

- mysql 递归次数限制由 cte_max_recursion_depth 控制。
- pg sqlite 没有递归次数限制。
- oracle 能够检测出递归死循环。
- mss 默认递归100次，可以在查询中使用 MAXRECURSION 选项设置。
- 递归终止的 WHERE 条件要在 CTE 的定义中，不能通过主查询实现。

## 参考

- [理解 MySQL with recursive 的执行过程](https://blog.chives.life/post/mysql-with-recursive)

通用表表达式

包括并集（Union）、交集（Intersect）和差集（Except）。执行SQL集合运算时，集合操作中的两个查询结果需要满足以下条件：

- 两个查询结果集中字段的数量必须相同。
- 两个查询结果集中对应字段的类型必须匹配或兼容。

注意

- SQLite 使用动态数据类型，不要求字段类型匹配或兼容。
- 某些数据库（例如 MySQL）可能会尝试执行隐式的类型转换。



## 交集求同

![图8.1 交集求同](https://s2.loli.net/2024/12/12/ojKUMBx1W2sy8Ti.png)

```sql
SELECT column1, column2, ...
FROM table1
INTERSECT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;
```

- DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
- ALL 表示保留合并结果中的重复记录。
- 默认值为 DISTINCT。
- mysql 不支持 INTERSECT。
- pg 完整支持 DISTINCT 和 ALL。
- oracle 21c 开始支持 ALL 选项。
- 其他数据库支持简写的 INTERSECT。

```sql
-- oracle mss pg sqlite
SELECT id, name
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;
```

交集运算通常可以改写为[内连接](https://blog.chives.life/post/sql-join-types#%E5%86%85%E8%BF%9E%E6%8E%A5%20Inner%20Join)。

- mysql 可以用这种方法实现交集。

```sql
SELECT DISTINCT t1.id, t1.name
FROM t_set1 t1
JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name)
```

## 并集存异

![图8.2 并集存异](https://s2.loli.net/2024/12/12/d8mCZMon1VzilAy.png)

```sql
SELECT column1, column2, ...
FROM table1
UNION [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;
```

- DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
- ALL 表示保留合并结果中的重复记录。
- 默认值为 DISTINCT。

```sql
SELECT id, name
FROM t_set1
UNION
SELECT id, name
FROM table2;
```

并集运算可以改写为[全外连接](https://blog.chives.life/post/sql-join-types#%E5%A4%96%E8%BF%9E%E6%8E%A5%20Outer%20Join)。

```sql
-- oracle mss pg
-- COALESCE 返回第一个非空值
SELECT COALESCE(t1.id, t2.id), COALESCE(t1.name, t2.name)
FROM t_set1 t1
FULL JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name);
```

- mysql sqlite 不支持全外连接。
- mysql sqlite 会进行隐式数据类型转换。

```sql
-- mysql sqlite
-- mysql 将第一个查询返回的字段转换为字符串类型
-- sqlite 将第二个查询返回的字段转换为整数类型
SELECT 1 AS id
UNION ALL
SELECT 'sql' AS name;
```

## 差集排他

![图8.3 差集排他](https://s2.loli.net/2024/12/12/iBpFxO9QJbLcUDR.png)

```sql
SELECT column1, column2, ...
FROM table1
EXCEPT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;
```

- DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
- ALL 表示保留合并结果中的重复记录。
- 默认值为 DISTINCT。
- mysql 不支持 EXCEPT 运算符。
- oracle 21c 开始支持 EXCEPT 关键字，其以前的版本使用等价的 MINUS 运算符。
- oracle 21c 开始支持 ALL 选项。
- 只有 pg 支持完整的 DISTINCT 和 ALL 选项。
- 其他数据库支持简写的EXCEPT。

```sql
-- oracle 19c以及更早
SELECT id, name
FROM t_set1
MINUS
SELECT id, name
FROM t_set2;
```

差集运算可以改写为[左外连接或右外连接](https://blog.chives.life/post/sql-join-types#%E5%A4%96%E8%BF%9E%E6%8E%A5%20Outer%20Join)。

- mysql 可以用这种方式实现差集。

```sql
SELECT t1.id, t1.name
FROM t_set1 t1
LEFT JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name)
WHERE t2.id IS NULL;
```

## 集合运算与排序

想要对集合运算的结果进行排序操作，必须将 ORDER BY 子句写在整个查询语句的最后，集合运算符之前的 SELECT 语句中不能出现排序子句。

```sql
SELECT id, name
FROM t_set1
UNION ALL
SELECT id, name
FROM t_set2
ORDER BY id;
```

## 运算符的优先级

- 按照SQL标准，交集运算符（INTERSECT）的优先级高于并集运算符（UNION）和差集运算符（EXCEPT）​。但是 Oracle 和 SQLite 中所有集合运算符的优先级相同。
- 相同的集合运算符按照从左至右的顺序执行。
- 某些数据库支持使用括号调整多个集合运算符的执行顺序。

```sql
-- mss pg sqlite
SELECT 1 AS n
UNION ALL
SELECT 1
INTERSECT
SELECT 1;
-- mss pg
/*
n
-
1
1
*/

-- sqlite
/*
n
-
1
*/

--oracle
SELECT 1 AS n FROM dual
UNION ALL
SELECT 1 FROM dual
INTERSECT
SELECT 1 FROM dual;
-- oracle
/*
n
-
1
*/

-- oracle 需要加上 dual 表
SELECT 1 AS N
UNION ALL
SELECT 1
UNION
SELECT 1;
/*
n
-
1
*/
SELECT 1 AS N
UNION
SELECT 1
UNION ALL
SELECT 1;
/*
n
-
1
1
*/

-- 使用括号修改顺序
-- mss pg，oracle 需要加上 dual 表
SELECT 1 AS N
UNION ALL
(SELECT 1
UNION
SELECT 1);
/*
n
-
1
1
*/
```

- MySQL 和 SQLite 目前不支持修改集合运算符优先级。

## 参考

- [Set operations (SQL)](https://en.wikipedia.org/wiki/Set_operations_(SQL))