主页 / dba / 问题 / 336080
Accepted
Ashley
Asked: 2024-02-21 11:52:14 +0800 CST

选择随机记录，直到超过累计总数

  • 772

我有一个带有id,skill_level和duration列的视频表，我正在尝试编写一个查询，该查询将返回总持续时间超过 的不同视频的播放列表X。

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;

这有效并返回以下内容：

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

但理想情况下，我想返回足够多的记录，X但只能超过一条。6375274c1cff4a7e31b04a0d下面让我们结束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

是否可以在 sqlite 中编写查询来实现此目的？

query

1 个回答

  1. Best Answer

    换句话说，任务是获取总和小于阈值的记录列表，并添加一条超过阈值的记录？

    with这可以通过使用andunion子句来实现：

    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

    或者您可以进行预选：

    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

相关问题