@copper20 这个 SQL ，如何检索多个值呢？

city=["234567","345678"]

county=["7345678","6234567"]

@sunmoon1983 我很好奇，不是 省 一对多 市 一对多 县 吗？ 为啥存了 县，还要存 市 和 省 呢？

@sunmoon1983 你要求速度吗？还是直接全表扫描？

可以接受全表扫描的话，直接 JSON_CONTAINS 呗


WITH
　 DATA(province, city, county) AS (
　　 VALUES
　　　 ROW(12345, '["123456","234567","345678"]', '["5123456","6234567","7345678"]'),
　　　 ROW(12345, '["123456","2345678","345678"]', '["5123456","6234567","7345678"]')
　)

SELECT *
FROM DATA
WHERE province = 12345
　 AND JSON_CONTAINS(city, CAST('["234567","345678"]' AS JSON))
　 AND JSON_CONTAINS(county, CAST('["7345678","6234567"]' AS JSON))

@simonlu9 再修正一下，10 楼 3.2 计算，这个表更应该是顺序插入，那预留 1/16 后，叶节点可存约 590 行，

若用 3.2：75~79 + (ceil(20000 / 590) ~ 20000) * 1~2 = 109 ~ 40079 次 IO

如果固态 16KB IOPS 有 13W ，那每秒最多可查询 3 ~ 1200 个类似这样的人的所有动态 ID 列表

@simonlu9 不对，「权限类型，作者 ID 」咋能成唯一索引呢。。只能是索引


另外，如果是这样设计，或许你的会比我 3 楼 的设计快：

动态表『主键「动态 ID 」，索引「权限类型，作者 ID ，允许用户 IDs JSON 」，动态内容 TEXT ，…… 』


因为每条动态不用回『动态表』查权限，也不用查『其他表』是否存在该权限了

「允许用户 IDs 」字符串操作，应该比一次 IO ，快几个数量级吧

@simonlu9

> 主键如果设那么长考虑插入时候分裂情况

我 3 楼的设计，直接按照『最差情况』，全部『随机插入』，叶节点『全裂成一半』考虑的


> 允许用户（ at_user_id ）最好是建另外一个表，字符串查找不建议

我打完下面这堆，才看到你的回复。最后计算，也大体验证你所说，『单独表』更可能比『字符串查找』快




如果是我 8 楼所认为的那样设计，那么：

唯一索引『主键「权限类型，作者 ID 」，动态 ID 』，叶节点每行记录 14 字节。

随机插入情况下，一个叶节点一半空间可用，可存约 585 行。



再拿 3 楼的 3W 条动态+1W 条<指定><他人 ID>可见动态（这对 3 楼的设计无影响，且合情合理），计算下硬盘 IO：


1. 花 1 ~ 2 次 IO ，查询其他表，来确定 <他人和自己是好友>

2. 查询 <公开>、<朋友可见> 每种类型的前 585 条记录，各花费 1 ~ 2 次 IO 。往后每 585 条，再花费 1 次 IO 。

3. 查询 <指定> 类型，和第 2 步类似，但还要确定自己是否在权限内：


3.1 「允许用户 IDs 」 JSON

每条动态，都要花 1 ~ 2 次 IO （偏向 2 ~ 3 次，此表很容易变深）回『动态表』，获取「允许用户 IDs 」，然后 find_in_set 或 json 函数确定。

假设『动态表』每行记录 0.5KB （不大吧），顺序插入情况下，叶子节点预留 1/16 空间，每个叶子节点可存 30 行

运气好，用户连发 30 条动态，全在一个叶子节点里，能命中缓存 29 次。运气差，无法命中缓存


3.2 『用户动态指定可见表』

每条动态，都要花 1 ~ 2 次 IO ，exists

此表叶节点每行记录 26 字节，随机插入情况下，一半空间可用，可存约 315 行。

运气好，用户连发 315 条动态，且每条动态都只指定一人可见，则可全在一个叶节点里。反之分散各处，无法命中缓存



final. 总计：1~2 + 2 * (1~2 + ceil(10000 / 585)) + (1~2 + ceil(20000 / 585) + …) = 75 ~ 79 + …

若用 3.1：75~79 + (ceil(20000 / 30) ~ 20000) * 1~2 = 742 ~ 40079 次 IO

若用 3.2：75~79 + (ceil(20000 / 315) ~ 20000) * 1~2 = 139 ~ 40079 次 IO

如果固态 16KB IOPS 有 13W ，那每秒最多可查询 3 ~ 1000 个类似这样的人的所有动态 ID 列表



看起来，是因为每条动态，都要验证权限，导致的回表 /查表次数太多

当然，指定某人可见的动态，应该不会这么多。可以算出个阈值，超过此值这种设计不划算



我 3 楼那样的设计，是将同类动态的行记录（某人公开、某人私有、某人朋友可见、某人指定他人 1 ，某人指定他人 2 ，……），尽可能凑在一起，实现：

1. 能迅速跳过某些类别的所有行记录（如，跳过某人私有、某人指定非自己的其他所有人）
2. 能迅速定位到某一类的第一行
3. 能利用上 B+ 树的叶子节点双向链表的特性，迅速遍历完某一类的余下行


这样的表，用文件系统类比，就是（如下），要啥类别，就直接读那个文件（叶子节点），直接跳过其他所有：

用户动态权限文件夹 /
　用户 A/
　　公开动态 ID 列表.txt
　　私有….txt
　　朋友可见….txt
　　指定可见 /
　　　用户 B 可见….txt
　　　用户 C 可见….txt
　用户 B/
　　……

@simonlu9 这样设计，会有何问题呢？

另外，你是在动态表上，建唯一索引吗？

这样：动态表『主键「动态 ID 」，唯一索引「权限类型，作者 ID 」，允许用户 IDs JSON ，动态内容 TEXT ，…… 』？

或「允许用户 IDs 」独立成一个「用户动态指定可见表」『主键「动态 ID ，允许用户 ID 」』？

@shadow1949 用不了窗口函数，强行自己模拟，代码看起来会很臭。。


换个思路，如果你能自己维护个『第几个工作日表』，也能很舒服。比如：

　　日期　　第几个工作日
—————　———————
09-09 周五　 1
09-10 中秋　（不要这行）
09-11 周日　（不要这行）
09-12 周一　（不要这行）
09-13 周二　 2
09-14 周三　 3
09-15 周四　 4
09-16 周五　 5
09-17 周六　（不要这行）


『 大致 SQL （排版原因，记得去掉每行开头的 全角空格）』

WITH

　 workday_data(date, nth, num) AS (
　　 SELECT date, nth, num
　　 FROM nth_workday LEFT JOIN orig_data USING(date)
　)

SELECT date, num,
　　　　(SELECT avg(COALESCE(num, 0)) FROM workday_data WHERE nth BETWEEN today.nth - 4 AND today.nth) avg_5,
　　　　(SELECT avg(COALESCE(num, 0)) FROM workday_data WHERE nth BETWEEN today.nth - 9 AND today.nth) avg_10
FROM workday_data today
WHERE num IS NOT NULL;

@huzhikuizainali 我在想，会不会是你不明白，

同样是“重复”，为何有的用递归，有的用循环？

递归还有一系列入栈出栈操作，既占内存，又耗性能，这货存在意义是什么？！

@huzhikuizainali 走迷宫用深度优先遍历啊

@huzhikuizainali 单纯的循环不会进栈

或许，你可以试着，用循环去解决一些，原本用递归干的活儿

比如，写个走迷宫。
再如，用递归下降去解析个 json

可能写多几个，你就不爱用循环+自己维护的栈，去模拟语言实现好的递归了

@simonlu9 诶，突然发现，主键设成那样，不就一堆重复的了。。脑子瓦特了

应该是『主键：「作者 ID ，权限类型 TINYINT ，允许用户 ID ，动态 ID 」，时间 』

但叶子节点的结论不变（因为还是 35 字节 / 行）


你说的『 showWith ，user_id 』索引，是『权限类型，作者 ID 』索引的意思吗？

你不用语言的栈来实现递归，那就用自己的栈来实现循环呗（除非可优化的尾递归）

关系数据库新手求问，这样设计，性能会很差吗？

用户动态权限信息表『主键：「作者 ID ，权限类型 TINYINT ，允许用户 ID 」，时间，动态 ID 』

（假设使用 MySQL 的 Innodb 引擎 Dynamic 或 Compact 行格式，则该表的叶节点中，每行记录占 35 字节）


场景一，按时间倒序，获取访问他人主页时，应该能看到的「动态 ID 」列表

select 动态 ID
from 用户动态权限信息表
where 作者 ID = <他人 ID>
　 and (权限类型 = <公开> or
　　　(权限类型 = <指定> and 允许用户 ID = <自己 ID>) or
　　　(exists <他人和自己是好友> and 权限类型 = <朋友>))
order by 时间 desc;


假设此表 B+ 树有 3 ~ 4 层高，前两层容易被缓存，且都是随机插入，即每个叶节点只有一半可用（能存约 234 行）

如果应能看到他人主页 公开、指定自己、朋友可见『各』 1W 条动态，对于此条查询，我设想会发生的硬盘 IO 次数：


1. 花 1 ~ 2 次 IO ，查询其他表，来确定 <他人和自己是好友>

2. 查询 <公开>、<指定><自己 ID>、<朋友可见> 每种类型的前 234 条记录，各花费 1 ~ 2 次 IO （从树根向叶子查）

往后每查询 234 条，再花费 1 次 IO （叶子是双向链表）。则总计 3 * ( 1~2 + ceil(10000 / 234)) = 132 ~ 135 次 IO


总结：若某人有（除自己可见外）各种类型『共』 3W 条动态，为获得这些动态 ID 列表，需读取硬盘 130 多次

如果固态 16KB IOPS 有 13W ，那每秒最多可查询 1000 个类似这样的人的所有动态 ID 列表

不知算得对不对

这……是一个函数内揉了太多内容，还是不同含义的变量都用同一个命名表示了？

@shadow1949 ，改写成了 MySQL ，将查询放进了一个视图中。

如果用 SQL 的话，以后一行「 select * from view_xxx 」就可得到结果了

使用前，记得将「调休」「非周末的其他假日」添加进『 holiday 表』



『「 select * from view_xxx 」结果』

　 date 　　 num 　 avg_5 　 avg_10
—————　——　————　————
2022-09-01 　 12 　 12.0000 　 12.0000
2022-09-20 　 11 　 02.2000 　 01.1000
2022-09-21 　 05 　 03.2000 　 01.6000
2022-09-22 　 23 　 07.8000 　 03.9000
2022-09-23 　 42 　 16.2000 　 08.1000



『 MySQL 语法（排版原因，记得去掉每行开头的 全角空格）』


-- 节假日数据（默认周六周日是假日。若有调休、其他假日，在此表指定）
CREATE TABLE holiday(date DATE PRIMARY KEY, is_holiday BOOL NOT NULL) AS
　 SELECT * FROM (
　　 VALUES
　　　-- 中秋放假
　　　 ROW('2022-09-12', true),
　　　-- 国庆放假和调休
　　　 ROW('2022-10-01', true), ROW('2022-10-04', true), ROW('2022-10-07', true),
　　　 ROW('2022-10-02', true), ROW('2022-10-05', true), ROW('2022-10-08', false),
　　　 ROW('2022-10-03', true), ROW('2022-10-06', true), ROW('2022-10-09', false)
　) AS v(date, is_holiday);

-- 原始数据（日期、数据）
CREATE TABLE orig_data(date DATE PRIMARY KEY, num INT NOT NULL) AS
　 SELECT * FROM (
　　 VALUES
　　　 ROW('2022-09-01', 12), ROW('2022-09-03', 22),
　　　 ROW('2022-09-20', 11), ROW('2022-09-21', 5),
　　　 ROW('2022-09-22', 23), ROW('2022-09-23', 42),
　　　 ROW('2022-09-24', 11)
　) AS v(date, num);

-- 原始数据的各种平均值视图
CREATE VIEW avgs_of_data AS

　 WITH RECURSIVE

　　-- 根据原始数据的日期范围，生成日历
　　-- （如果超过 1000 天，记得调整 cte_max_recursion_depth ）
　　 calendar(date) AS (
　　　 SELECT min(date)
　　　　 FROM orig_data
　　　 UNION ALL
　　　 SELECT DATE_ADD(date, INTERVAL 1 DAY)
　　　　 FROM calendar
　　　 WHERE date < (SELECT max(date) FROM orig_data)
　　),
　　
　　-- 根据日历，生成工作日数据（日期、该天是否为工作日）
　　 workday(date, is_workday) AS (
　　　 SELECT date, COALESCE(NOT is_holiday, weekday(date) < 5)
　　　 FROM calendar LEFT JOIN holiday USING(date)
　　),

　　-- 为每个工作日，计算最近 5 或 10 个工作日内的平均值（日期、数值、5 工作日均值、10 工作日均值）
　　 avgs_of_workday(date, num, avg_5, avg_10) AS (
　　　 SELECT date, num,
　　　　　　 avg(COALESCE(num, 0)) OVER (ORDER BY date ROWS 4 PRECEDING),
　　　　　　 avg(COALESCE(num, 0)) OVER (ORDER BY date ROWS 9 PRECEDING)
　　　　 FROM workday LEFT JOIN orig_data USING(date)
　　　 WHERE is_workday
　　)

　-- 去除没有数据的工作日
　 SELECT *
　　 FROM avgs_of_workday
　 WHERE num IS NOT NULL;


MySQL 不支持在「窗口函数」中使用「 group_concat 」，所以没有「(11+5+23) / 5 」之类的结果了。

但对你的需求无影响，只是方便查看平均值的计算过程是否正确。

@shadow1949 搞出来了，『「一条」普通 SQL 语句』

不用「建表、存储过程、事务」，连 SQLite 都能胜任的，普通语句。

去掉「节假日数据」和「测试数据」后，大概 20 行


『 SQLite 语法（排版原因，记得去掉每行开头的 全角空格），改成 MySQL 应该也很容易』

WITH RECURSIVE

　-- 节假日数据（默认周六周日是假日。若有调休、其他假日，在此表指定）
　 holiday(date, is_holiday) AS (
　　 VALUES
　　　('2022-09-12', true),
　　　('2022-10-01', true), ('2022-10-04', true), ('2022-10-07', true),
　　　('2022-10-02', true), ('2022-10-05', true), ('2022-10-08', false),
　　　('2022-10-03', true), ('2022-10-06', true), ('2022-10-09', false)
　),

　-- 测试数据（日期，数据）
　 test_data(date, num) AS (
　　 VALUES
　　　('2022-09-01', 12), ('2022-09-22', 23),
　　　('2022-09-03', 22), ('2022-09-23', 42),
　　　('2022-09-20', 11), ('2022-09-24', 11),
　　　('2022-09-21', 5)
　),

　-- 九月份日历（此表有 30 行数据）
　 calendar(date) AS (
　　 SELECT '2022-09-01'
　　 UNION ALL
　　 SELECT date(date, '+1 day')
　　　 FROM calendar
　　 WHERE date < '2022-09-30'
　),

　-- 根据日历，生成工作日数据（日期，该天是否为工作日）
　 workday(date, is_workday) AS (
　　 SELECT date, COALESCE(NOT is_holiday, 0 + strftime('%w', date) BETWEEN 1 AND 5)
　　　 FROM calendar LEFT JOIN holiday USING(date)
　)

-- 1. 根据工作日日历，测试数据中丢弃节假日的行，补充其他缺失工作日的行（这些新行的 num IS NULL ）
-- 2. 利用范围为当前行及之前 4 行的窗口函数，按照日期顺序，滑动计算窗口内的平均值
-- 3. 丢弃第 1 步中，补充的行（即 num IS NULL 的行）

SELECT *
　 FROM (
　 SELECT date, num, avg(COALESCE(num, 0)) OVER win avg,
　　　　 format('(%s) / %d', group_concat(num, '+') OVER win, COUNT(*) OVER win) expr
　　 FROM workday LEFT JOIN test_data USING(date)
　 WHERE is_workday
　 WINDOW win AS (ORDER BY date ROWS 4 PRECEDING)
)
WHERE num IS NOT NULL;


『输出』

　　 date 　　 num 　 avg 　　　　 expr
—————　——　——　—————————
2022-09-01 　 12 　 12.0 　(12) / 1
2022-09-20 　 11 　 02.2 　(11) / 5
2022-09-21 　 05 　 03.2 　(11+5) / 5
2022-09-22 　 23 　 07.8 　(11+5+23) / 5
2022-09-23 　 42 　 16.2 　(11+5+23+42) / 5

@shadow1949 ，为嘛你第二条附言里的『 query sql 』，结果看着不太对呢？

id 　　　 date 　　 num 　 avg_num
——　————　———　————
01 　 2022-09-01 　 12 　 12.0000
13 　 2022-09-20 　 11 　 2.2000 『不应该是 11 / 1 = 11 么』
14 　 2022-09-21 　 05 　 3.2000 『(11+5) / 2 = 8 ？』
15 　 2022-09-22 　 23 　 7.8000 『(11+5+23) / 3 = 13 ？』
16 　 2022-09-23 　 42 　 16.2000 『(11+5+23+42) / 4 = 20.25 ？』


结果里缺失了『 2022-09-03 』『 2022-09-24 』，看来 24 楼里的问题，你的计划应该是『节假日不出现在结果列表里』

我总觉得可以『「一条」普通 SQL 语句』搞定这个问题。等我试试

@shadow1949 ，我还好奇一件事

假如表中就两项：

1. 2022-08-05 （周五） num: 100
2. 2022-08-06 （周六） num: 200

周六前五天，num 的平均值，你计划算出啥结果？

1. null
2. 不出现在结果列表
3. (100) / 1 = 100 （因为跳过周末）
4. (200 + 100) / 2 = 150 （因为包括当天）