SUM valores GROUP BY exclusivos no sqlite e conta grupos de valores consecutivos

Usar um CTE que agrupa por pid,tid primeiro ajudará a produzir os resultados esperados

WITH CTE AS
(
SELECT
    pid,
    tid,
    SUM(distinct duration) AS SumDistinctDur,
    GROUP_CONCAT(id) AS Ids
FROM
    distinct_example
GROUP BY
    pid, tid
)  
SELECT
    pid,
    COUNT(tid) AS num_times,
    SUM(SumDistinctDur) AS exposure_time,
    GROUP_CONCAT(Ids) AS rows,
    GROUP_CONCAT(tid) AS unique_timesteps
FROM
    CTE
GROUP BY
    pid

violino

exposure_time: garantindo a exclusividade

Como você analisou corretamente, seu DISTINCT durationretorna apenas uma ocorrência quando dois tidcompartilham o mesmo duration. O que naturalmente corresponde à sua necessidade seria o do PostgreSQLDISTINCT ON , que não existe no SQLite, então você terá que emulá-lo.

Poderíamos usar subseleções com LIMIT 1(para obter 1 valor para cada tid selecionado),
mas prefiro usar Expressões de Tabela Comuns que podem ser vistas como criação sequencial de tabelas temporárias intermediárias, ajudando a manter a organização (você constrói sua consulta progressivamente, tabela intermediária por tabela intermediária) e facilitando o diagnóstico (você pode, select *a partir de qualquer tabela intermediária, verificar se a etapa do processo está correta em relação às suas expectativas).

Então, primeiro renormalizaremos todos os seus dados em CTEs , garantindo que eles contenham apenas valores únicos (claro, se você ainda tiver sua tabela tid/ original, sinta-se à vontade para começar a partir dela) e, em seguida, somaremos as tabelas sem (porque temos certeza de que temos apenas uma linha por entrada / e por casal / ):duration
DISTINCTtiddurationtidpid

WITH
    -- Get only 1 tid / pid pair 
    pidtid AS (SELECT DISTINCT pid, tid FROM distinct_example),
    -- Reconstitute single tids
    tid as (SELECT DISTINCT tid, duration FROM distinct_example),
    -- Now sum up pid by pid
    sum as
    (
        SELECT pid, COUNT(1) num_times, SUM(duration) exposure_time
        FROM tid JOIN pidtid USING (tid)
        GROUP BY pid
    )
SELECT * from sum;

visits: função de janela para contar a distância entre as mesmas entradas pid

Para o seu caso visits, você está certo, uma função de janela será a maneira mais fácil (também poderíamos unir a tabela a si mesma ON b.id BETWEEN a.id - 6 and a.id - 1 WHERE b.pid = a.pidpara encontrar predecessores próximos). Não podemos
comparar linhas anteriores com a linha atual usando uma função de janela ( ), então particionaremos por pid e usaremos a ordem deles dentro do conjunto de dados completo, que eu entendo ser , para ver a que distância cada entrada está.MIN(CASE WHEN pid = CURRENT.pid THEN 0 ELSE 1 END) OVER (ORDER BY id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS INCREASE
id

WITH
    prev AS
    (
        SELECT
            id, pid, tid,
            CASE WHEN id <= 6 + LAG(id) OVER (PARTITION BY pid ORDER BY id) THEN 0 ELSE 1 END AS new_visit
        FROM distinct_example
    ),
    visits AS (SELECT pid, SUM(new_visit) visits FROM prev GROUP BY pid)
SELECT * FROM visits;

Contudo

Agora podemos juntar as nossas duas mesas (cada uma delas garantindo uma entrada por pid):

WITH
    -- Get only 1 tid / pid pair 
    pidtid AS (SELECT DISTINCT pid, tid FROM distinct_example),
    -- Reconstitute single tids
    tid AS (SELECT DISTINCT tid, duration FROM distinct_example),
    -- Now sum up pid by pid
    sum AS
    (
        SELECT pid, COUNT(1) num_times, SUM(duration) exposure_time
        FROM tid JOIN pidtid USING (tid)
        GROUP BY pid
    ),
    -- Start another "thread" for visits (another = we do not SELECT from the previous CTEs, we start straight from distinct_example)
    -- The following CTE is optional, it recomputes ids *if they are not sequential*.
    -- (some RDBMS allow choosing the same name as the original table to transparently override any use of the original table in the subsequent SELECTs)
    distexreindexed AS (select row_number() OVER (ORDER BY id) id, pid, tid, duration FROM distinct_example),
    prev AS
    (
        SELECT
            id, pid, tid,
            CASE WHEN id <= 6 + LAG(id) OVER (PARTITION BY pid ORDER BY id) THEN 0 ELSE 1 END AS new_visit
        FROM distexreindexed -- ← Switch to the reindexed table.
    ),
    visits AS (SELECT pid, SUM(new_visit) visits FROM prev GROUP BY pid)
SELECT sum.*, visits FROM sum JOIN visits USING (pid) ORDER BY pid;

Provavelmente poderíamos ter compactado (agrupando algumas consultas), mas ajudar esses dois problemas distintos a permanecerem distintos na consulta final facilitará a manutenção.

Tudo funciona nesse violino .