Início / dba / Perguntas / 336080
Accepted
Ashley
Asked: 2024-02-21 11:52:14 +0800 CST

Selecione registros aleatórios até que um total cumulativo seja excedido

  • 772

Eu tenho uma tabela de vídeos com uma coluna e ide estou tentando escrever uma consulta que retornará uma lista de reprodução de vídeos distintos com duração total superior a .skill_leveldurationX

select _id, duration, running_total
from (select *, sum(duration) over (order by RANDOM()) as running_total
      from videos
      where skill_level in ('beginner', 'superbeginner') AND watched IS NULL
     )
where running_total <= 7200;

Isso funciona e retorna o seguinte:

5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985

Mas, idealmente, quero retornar registros suficientes para exceder, Xmas apenas um. 6375274c1cff4a7e31b04a0dabaixo nos coloca 7200.

5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985
6375274c1cff4a7e31b04a0d|1138|8123

É possível escrever uma consulta para conseguir isso no sqlite?

query

1 respostas

  1. Best Answer

    Em outras palavras, a tarefa é obter uma lista de registros com uma soma menor que o limite e um registro adicional para ultrapassar o limite?

    Isso pode ser conseguido usando cláusulas withe union:

    with
below_threshold as (
  select _id, duration, running_total
  from (select *, sum(duration) over (order by RANDOM()) as running_total
        from videos
        where skill_level in ('beginner', 'superbeginner') AND watched IS NULL
       )
  where running_total <= 7200
),
one_more as (
  select _id, duration
  from videos
  where skill_level in ('beginner', 'superbeginner') AND watched IS NULL
        and _id not in (select _id from below_threshold)
  limit 1
  order by random()
)
select _id, duration from below_threshold
union
select _id, duration from one_more

    ou você pode fazer uma pré-seleção:

    with
to_consider as (
  select _id, duration
  from videos
  where skill_level in ('beginner', 'superbeginner') AND watched IS NULL
),
below_threshold as (
  select _id, duration, running_total
  from (select *, sum(duration) over (order by RANDOM()) as running_total
        from to_consider
       )
  where running_total <= 7200
),
one_more as (
  select _id, duration, (select sum(duration) from below_threshold)+duration as running_total
  from to_consider
  where _id not in (select _id from below_threshold)
  limit 1
  order by random()
)
select _id, duration, running_total from below_threshold
union
select _id, duration, running_total from one_more
    • 1

relate perguntas