为每个组或默认值选择前x个记录

efzxgjgh 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(442)

我有以下模式：

users:

id email
1  'user.one@test.com'
2  'user.two@test.com'

video_group:

id title
1  'Group 1'
2  'Group 2'

videos:

id group_id rank title
1  1        1    'Group 1 - Video 1'
2  1        2    'Group 1 - Video 2'
3  2        1    'Group 2 - Video 1'

user_video_play_times:

video_id user_id time last_update
2        1       12   01-02-2018
1        1       120  01-01-2018

我要去拿那个 time , user_id , video_id ，和 group_id 用户在特定组中播放的最后一段视频，但如果 user_video_play_times 对于组，应返回排名最低的视频。例如：

user_id group_id video_id time
1       1        2        12    -- user.one + group 1
1       2        3        0     -- user one + group 2

这是我到目前为止的疑问：

SELECT
   pt.user_id user_id,
   v.id       video_id,
   g.id       group_id,
   pt.time    time
FROM
   videos v
   INNER JOIN video_groups g ON g.id = v.group_id
   LEFT JOIN user_video_play_times pt ON 
      pt.video_id = v.id AND 
      pt.user_id = 1
   LEFT JOIN (
      SELECT 
         g.id AS g_id,
         MAX(pt.last_update) AS pt_last_update
      FROM
         user_video_play_times pt
         INNER JOIN videos v ON v.id = pt.video_id
         INNER JOIN video_groups g ON g.id = v.group_id
      WHERE
         pt.user_id = 1 AND
         g.id IN (1, 2)
      GROUP BY
         g.id
   ) lpt ON lpt.g_id = g.id AND lpt.pt_last_update = pt.last_update
WHERE
   g.id IN (1, 2)
GROUP BY
   g.id

有点用，但是。。。
添加 v.title 由于某种原因，列选择会扰乱结果，使所有内容只返回排名为1的视频。知道为什么吗？
这个查询是否可以优化，或者是否有其他更巧妙的方法来实现相同的结果？
非常感谢您的帮助！
我在这儿拉小提琴
更新1：
这个问题似乎只有在类型为os的列 text .

mysql optimization greatest-n-per-group

来源：https://stackoverflow.com/questions/53046794/select-top-x-records-for-each-group-or-default

1条答案

按热度按时间

wfsdck301#

因为您的db<>fiddle是针对mariadb版本10.3的；我想你们有可用的窗口功能。
我们可以用 Row_number() 函数的分区 group_id 根据定义的规则获取行号值。最新视频 last_update 值的行号为1，依此类推。如果没有播放视频，则秩值最小的视频的行号为1。
我们可以将这个结果集用作派生表，并且只考虑那些行号为1的行。

SELECT 
  dt.user_id, 
  dt.group_id, 
  dt.video_id, 
  dt.video_title, 
  dt.time 
FROM 
(
  SELECT
     pt.user_id AS user_id,
     g.id       AS group_id,
     v.id       AS video_id,
     v.title    AS video_title,  
     pt.time    AS time,  
     ROW_NUMBER() OVER(PARTITION BY v.group_id 
                       ORDER BY pt.last_update DESC, 
                                v.`rank` ASC) AS row_num 
  FROM videos AS v
  INNER JOIN video_groups AS g 
    ON g.id = v.group_id AND 
       g.id IN (1,2) 
  LEFT JOIN user_video_play_times AS pt 
    ON pt.video_id = v.id AND 
       pt.user_id = 1 
) AS dt 
WHERE dt.row_num = 1

db fiddle视图
结果：

| user_id | group_id | video_id | video_title       | time |
| ------- | -------- | -------- | ----------------- | ---- |
| 1       | 1        | 2        | Group 1 - Video 2 | 12   |
|         | 2        | 3        | Group 2 - Video 1 |      |

ps：注意 Rank 是保留关键字，应该避免将其用作列/表名。

赞(0）回复(0）举报 2021-06-18

我来回答

为每个组或默认值选择前x个记录

1条答案

相关问题

热门标签

最新问答