Início / user-1506763

jpmorr's questions

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

Sou um usuário iniciante de SQL e estou com dificuldades em uma sqliteconsulta sobre um arquivo GROUP BY. Aqui está um exemplo simplificado do meu problema usando os dados desta tabela:

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

Estou tentando contar o número de tids únicos para cada um pide somar o durationfor each pid. Embora tidseja único, seu correspondente durationnão é único. Observação: no caso real, durationé na verdade uma coluna unida tide estou mostrando apenas as colunas nas quais estou tentando operar aqui.

Também quero descobrir o número de "visitas" - se houver uma interrupção no "unique_timesteps" de mais de 5 tids, isso conta como uma visita separada - então, neste exemplo, pid=1teríamos 2 visitas, enquanto pid=2teríamos uma visita.

Aqui está o que espero que seja o resultado final:

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

Executo a seguinte consulta nos dados, mas ela não retorna os resultados esperados para o agrupamento.

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;

Aqui está o resultado onde estou adicionando as duas últimas colunas para contexto:

┌───────┬───────────┬────────────────────┬──────────────────────────────────────────────────┬──────────────────────┐
│  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 │
└───────┴───────────┴────────────────────┴──────────────────────────────────────────────────┴──────────────────────┘

Em cada caso, há 7 intervalos de tempo exclusivos registrados para cada um pid, conforme mostrado corretamente na segunda coluna. A terceira coluna deve ser a soma da durationcoluna para a respectiva group byoperação em pid. Isso deve resultar em (0,737999, 0,726300) como resultado para , exposure_timepois durationdeve ser somado em , unique_timestepsem vez de durations. No entanto, entendi mal o uso de DISTINCTdentro de , GROUP BYe uma das durações de pid=2foi negligenciada (0,11).

Acho que devo fazer alguma subconsulta para selecionar as linhas corretas para usar como índice, mas não consigo entender como fazer isso.

Não tenho ideia de como calcular o visitsresultado, mas presumo que isso poderia ser feito com algumas subconsultas e uma função de janela?

sql