求助较为复杂的查询语句的编写

有个data数据表如下：

id	time
1	10时01分01秒
2	10时29分01秒
3	10时06分01秒
5	10时05分01秒
5	10时25分01秒
7	10时15分01秒
7	10时45分01秒
8	10时41分01秒
9	10时50分01秒
10	10时51分01秒

time时间顺序是乱的，现在要查询的是30分钟内的最大样本数量，起止点未知，最终得出的结果有三组是6，则该查询的返回结果就是6.

tiesuoqiao 发表于 2024-7-1 20:22
思路如下，
步骤：
1.

把你的数据在Excel里改了time列的格式生成了CSV文件 Book2.csv:

id,time
1,10:01:01 AM
2,10:05:01 AM
3,10:06:01 AM
4,10:15:01 AM
5,10:25:01 AM
6,10:29:01 AM
7,10:41:01 AM
8,10:45:01 AM
9,10:50:01 AM
10,10:51:01 AM

python代码（用了duckdb + SQL)

1. import duckdb
   
2. 
   
3. con = duckdb.connect()
   
4. 
   
5. df=duckdb.read_csv('Book2.csv',  dtype = ["int", "time"])
   
6. 
   
7. SQL="SELECT MAX(c) FROM (SELECT id, count(*) AS c FROM (SELECT df.id, date_diff('minute', df.time, df2.time) AS difference FROM df, df AS df2 WHERE df.id != df2.id) AS p WHERE difference <= 30 AND difference >=-30 GROUP BY id);"
   
8. 
   
9. duckdb.query(SQL)

复制代码

最后结果

1. ┌────────┐
   
2. │ max(c) │
   
3. │ int64  │
   
4. ├────────┤
   
5. │      9 │
   
6. └────────┘

复制代码

提供查询语句思路即可，不需要纠结于时间格式的处理。

思路如下，
步骤：
1.
你把这个表和它自己product，就是cross join，得到下表
id  time   id1  time1

2.
得到的每行计算time和time1的时间差，取绝对值
3.
filter：绝对值>0且小于等于30秒；上步骤如果不取绝对值就是-30到+30之间，不等于0；或者第一步加个限制 id != id1
4.
按照id，count
5
取最大值

tiesuoqiao 发表于 2024-7-1 20:22
思路如下，
步骤：
1.

那些过滤自己join自己的步骤可以省略
最后一步取最大值减去1就行了

tiesuoqiao 发表于 2024-7-1 21:15
那些过滤自己join自己的步骤可以省略
最后一步取最大值减去1就行了

请问，按照您的思路，是这个写法不？
select max(count(a.id)) from (select a.id,a.time,b.id,b.time,dtime=a.time-b.time from data a,data b) filter(where dtime >0 and dtime <=30)

max和count都是聚合函数，好像不能嵌套？数据库环境是sqlite。后面对数据表起别名是不是和cross join效果一样？

tiesuoqiao 发表于 2024-7-2 07:40
把你的数据在Excel里改了time列的格式生成了CSV文件 Book2.csv:

id,time

感谢感谢，我试试

SQL里的date_diff就是计算两个时间的差，以分钟为单位
date_diff('minute', df.time, df2.time)

这是duckdb的函数
其他数据库的函数名字可能不同。

感谢感谢，按照你的例子思路，把问题解决了

tiesuoqiao 发表于 2024-7-2 10:38
SQL里的date_diff就是计算两个时间的差，以分钟为单位
date_diff('minute', df.time, df2.time)

感谢感谢，按照你的例子思路，把问题解决了