主页 / user-1506763

jpmorr's questions

Martin Hope
jpmorr
Asked: 2025-04-09 20:37:34 +0800 CST
  • 6

我是一个 SQL 新手，正在为一个sqlite查询而苦恼GROUP BY。以下是使用以下表数据解决此问题的简化示例：

id, pid, tid, duration
1, 1, 12, 0.099999
2, 1, 13, 0.105
3, 1, 14, 0.102
4, 1, 15, 0.1
5, 1, 22, 0.12
6, 1, 23, 0.101
7, 1, 24, 0.11
8, 2, 13, 0.105
9, 2, 14, 0.102
10, 2, 15, 0.1
11, 2, 16, 0.11
12, 2, 17, 0.11
13, 2, 18, 0.0995
14, 2, 19, 0.0998
15, 1, 12, 0.099999
16, 1, 13, 0.105
17, 1, 23, 0.101
18, 1, 24, 0.11
19, 2, 15, 0.1
20, 2, 16, 0.11
21, 2, 17, 0.11
22, 2, 18, 0.0995
23, 2, 19, 0.0998
24, 1, 13, 0.105
25, 1, 15, 0.1
26, 1, 22, 0.12
27, 1, 23, 0.101
28, 1, 24, 0.11
29, 2, 13, 0.105
30, 2, 14, 0.102
31, 2, 15, 0.1
32, 2, 16, 0.11
33, 2, 19, 0.0998

tid我正在尝试计算每个 的唯一值的数量pid，并duration为每个求和pid。虽然tid是唯一的，但它对应的duration并非唯一。注意：在实际情况下，duration实际上是 上的连接列tid，我在这里只显示了我尝试操作的列。

我还想弄清楚“访问”的次数 - 如果“unique_timesteps”中间隔超过 5tid秒，那么这将算作一次单独的访问 - 因此在这个例子中，pid=1会有 2 次访问，而pid=2会有 1 次访问。

我期望的最终结果如下：

┌───────┬───────────┬────────────────────┬────────┐
│  pid  │ num_times │   exposure_time    │ visits │
│ int64 │   int64   │       double       │  int64 │
├───────┼───────────┼────────────────────┼──────-─┤
│     1 │         7 │ 0.7379990000000001 │    2   │
│     2 │         7 │ 0.7263000000000001 │    1   │
└───────┴───────────┴────────────────────┴────────┘

我对数据运行以下查询，但它没有返回分组的预期结果。

SELECT
    pid,
    COUNT(DISTINCT(tid)) AS num_times,
    SUM(DISTINCT(duration)) AS exposure_time,
    GROUP_CONCAT(DISTINCT(id)) AS rows,
    GROUP_CONCAT(DISTINCT(tid)) AS unique_timesteps
FROM
    distinct_example
GROUP BY
    pid
ORDER BY
    pid;

这是我添加最后两列作为上下文的结果：

┌───────┬───────────┬────────────────────┬──────────────────────────────────────────────────┬──────────────────────┐
│  pid  │ num_times │   exposure_time    │                       rows                       │   unique_timesteps   │
│ int64 │   int64   │       double       │                     varchar                      │       varchar        │
├───────┼───────────┼────────────────────┼──────────────────────────────────────────────────┼──────────────────────┤
│     1 │         7 │ 0.7379990000000001 │ 24,4,2,7,25,27,16,1,15,18,3,5,28,6,17,26         │ 12,23,15,22,24,13,14 │
│     2 │         7 │             0.6163 │ 13,14,32,9,8,19,23,33,12,31,30,20,22,11,10,21,29 │ 16,19,17,13,14,18,15 │
└───────┴───────────┴────────────────────┴──────────────────────────────────────────────────┴──────────────────────┘

在每种情况下，每个时间步长都记录了7个唯一的时间步长，pid如第二列正确显示的那样。第三列应为duration对 进行相应group by操作的 列的总和pid。这应该给出 (0.737999, 0.726300) 作为 的结果，exposure_time因为duration应该是对 求和unique_timesteps，而不是对 唯一的 求和durations。但是，我误解了DISTINCT中的的用法GROUP BY，并且 的持续时间之一pid=2被忽略了（0.11）。

我想我应该做一些子查询来选择正确的行作为索引，但我不知道该如何做到这一点。

我不知道如何计算visits结果，但推测可以通过一些子查询和窗口函数来完成？

sql