php - 为每个类别打印观看次数最多的视频

Question

我有两个表

video(id,macro_categoria,categoria,titolo)

和

video_logs(id,idvideo,logs)

(存储视频被观看的次数)。关系是一个有多个id=>idvideo我想要这样的结果:

  macro_categoria:one {
          [1]=> video 1 details
          [2]=> video 2 details
          [3]=> video 3 details
          [4]=> video 4 details
        }
  macro_categoria:two {
          [1]=> video 1 details
          [2]=> video 2 details
          [3]=> video 3 details
          [4]=> video 4 details
        }

上面的数组为每个 macro_categoria 打印了四个观看次数最多的视频。观看视频的次数取自表 video_logs 的日志。目前我在这一点上卡住了:

SELECT c.macro_categoria,c.id, Count((v.id)) as Views
 FROM video c,video_log v
 WHERE c.id = v.idvideo
 GROUP BY c.id 
 ORDER BY Views DESC LIMIT 4

这 echo :

[0] => Array
        (
            [macro_categoria] => macro2
            [id] => 11
            [Views] => 47
        )

[1] => Array
    (
        [macro_categoria] => macro1
        [id] => 12
        [Views] => 23
    )

Answer 1

这是一个查询，用于选择每个类别的计数:

SELECT videos.id, cats.macro_categoria, videos.titolo, IFNULL(views.cnt, 0) AS cnt 
FROM (SELECT DISTINCT macro_categoria FROM `video`) cats 
JOIN (SELECT id, macro_categoria, titolo FROM video) videos ON videos.macro_categoria = cats.macro_categoria 
LEFT JOIN (SELECT idvideo, COUNT(idvideo) as cnt FROM video_log GROUP BY idvideo) `views` ON `views`.idvideo = videos.id 
ORDER BY cats.macro_categoria, cnt DESC

在这里，我们从类别列表开始，(INNER) 将视频连接到这些类别，然后 LEFT 连接观看次数，这样我们仍然有 NULL 结果，这样您就可以知道哪里没有观看。然后按类别和 View 降序排序。

如果您想在 PHP 中获得每个类别的前四位成员，您可以使用如下逻辑(有点伪代码):

$result = DB::query($sql);
$byCategory = [];
while ($row = DB::fetchAssoc($result)) {
    // Create array to hold category
    if (!isset($category[$result['macro_categoria']))
        $category[$result['macro_categoria'] = [];

    // Store record
    $byCategory[$result['macro_categoria']][] = $result;
}

// Trim results per category
foreach ($byCategory AS $cat=>$data)
    $byCategory[$cat] = array_slice($data, 0, 4);

array_slice 获取每个类别的前四个元素。只要结果集从服务器正确排序，这将返回最高值。