MySQL中复杂的计数查询

Question

我正在尝试找到特定用户拥有的视频点数。

以下是三个相关的表格：

CREATE TABLE `userprofile_userprofile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(100) NOT NULL,
   ...
 )

CREATE TABLE `userprofile_videoinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(256) NOT NULL,
  `uploaded_by_id` int(11) NOT NULL,
  ...
  KEY `userprofile_videoinfo_e43a31e7` (`uploaded_by_id`),
  CONSTRAINT `uploaded_by_id_refs_id_492ba9396be0968c` FOREIGN KEY (`uploaded_by_id`) REFERENCES `userprofile_userprofile` (`id`)
)

CREATE TABLE `userprofile_videocredit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `video_id` int(11) NOT NULL,
  `profile_id` int(11) DEFAULT NULL,
  KEY `userprofile_videocredit_fa26288c` (`video_id`),
  KEY `userprofile_videocredit_141c6eec` (`profile_id`),
  CONSTRAINT `profile_id_refs_id_31fc4a6405dffd9f` FOREIGN KEY (`profile_id`) REFERENCES `userprofile_userprofile` (`id`),
  CONSTRAINT `video_id_refs_id_4dcff2eeed362a80` FOREIGN KEY (`video_id`) REFERENCES `userprofile_videoinfo` (`id`)
)

videoinfo表是指当用户上传视频时，他将获得一个"uploaded_by“列表。videocredit表是给定电影的所有片头。它完全独立于上传电影(即，用户可以上传视频而不记自己的信用，用户可以在他没有上传的视频中记信用)。

在尝试查找用户已获得积分的视频计数时，我想要找到：

# videos a user has uploaded + # of non duplicate-video credits uploaded by others

举个例子:如果用户上传了5个视频，名为：

VideoME1, VideoME2, VideoME3, VideoME4, and VideoME5 
(total = 5 videos [`videoinfo.uploaded_by_id`])

并有以下视频片头：

VideoME1 (4 credits - director, writer, editor, choreographer)
VideoME2 (1 credit)
VideoOTHER1 (2 credits - writer, editor)
VideoOTHER2 (1 credit - writer)
(total = 8 video credits [`videocredit.profile_id`])

计数应为5(上传的视频)+2(其他人上传的非重复视频片头)= 7，如果用户没有视频片头，则应=0(即LEFT OUTER JOIN)。

我已经能够计算出每个上传/信用的计数，但不知道如何将两者结合起来并去掉重复的内容。要做到这一点，我需要什么SQL？谢谢。

顺便说一句，这是我目前拥有的每个(单个)计数：

mysql> SELECT full_name, v.video_id, COUNT(DISTINCT v.video_id) as cnt
    -> FROM userprofile_userprofile u LEFT OUTER JOIN userprofile_videocredit v
    -> ON u.id = V.profile_id
    -> GROUP BY full_name
    -> ORDER BY cnt DESC;

mysql> SELECT full_name, v.id, COUNT(v.uploaded_by_id) as cnt
    -> FROM userprofile_userprofile u LEFT OUTER JOIN userprofile_videoinfo v
    -> ON u.id = v.uploaded_by_id
    -> GROUP BY full_name
    -> ORDER BY cnt DESC;

Answer 1

X-Zero建议向数据添加“上传者信用”，这是保持查询简单的最佳方法。如果不能这样做，那么可以在userprofile_videoinfo和userprofile_videocredit之间进行内部连接，以便于消除重复项：

SELECT u.id, u.full_name, COUNT(DISTINCT v.video_id) as credit_count
FROM userprofile_userprofile u
LEFT JOIN (SELECT vi.video_id, vi.uploaded_by_id, vc.profile_id as credited_to_id
    FROM userprofile_videoinfo vi
    JOIN userprofile_videocredit vc ON vi.id = vc.video_id
    ) v ON u.id = v.uploaded_by_id OR u.id = v.credited_to_id
GROUP BY u.id, u.full_name
ORDER BY credit_count DESC

子查询在创建为视图时可能很有用。

Answer 2

基本上有两种方法可以做到这点：

1)添加'uploader‘作为他们可以归功的东西，并添加一个触发器来自动填充条目-只有一个表去，等等。

2)我相信下面的查询应该也能工作(这也会解决你的full_name问题)：

WITH credit_rollup (profile_id) as (SELECT profile_id
                                    FROM userprofile_videocredit 
                                    GROUP BY profile_id, video_id)
SELECT full_name, COALESCE((SELECT count(*) 
                            FROM credit_rollup as v
                            WHERE v.profile_id = u.id), 0) +
                  COALESCE((SELECT count(*)
                            FROM userprofile_videoinfo as v
                            WHERE v.uploaded_by_id = u.id), 0) as credits
FROM userprofile_userprofile
ORDER by credits DESC

不过，您可能希望删除每个表名前面的“userprofile”，而只将它们放入具有该名称的模式中。

编辑查询以仅计算每个视频的1个点数。

再看一遍这篇文章后，很明显，我漏掉了“重复”这个词的一个关键用法--因为用户上传视频并在视频中拥有某种信用(导演、编辑等)只被记了一次，而不是两次( OR)。

因此，下面的查询更符合要求(感谢@simon让我思考)：

WITH credit_rollup (uploaded_by_id, credited_to_id) 
                   AS (SELECT info.uploaded_by_id, credit.profile_id
                       FROM userprofile_videoinfo as info
                       JOIN userprofile_videocredit as credit
                       ON info.id = credit.video_id
                       GROUP BY info.uploaded_by_id, credit.profile_id)
SELECT usr.full_name, COALSECE((SELECT count(*)
                                 FROM credit_rollup as rollup
                                 WHERE rollup.uploaded_by_id = usr.id
                                 OR rollup.credited_to_id = usr.id), 0) as credits
FROM userprofile as usr
ORDER BY credits DESC

正如@simon所说，CTE (他使用子查询)可能对创建视图很有用(基本上，它列出每个人参与视频的一次，这是一件很方便的事情)。

Answer 3

如果我没有犯任何错误：

SELECT u.id
     , u.full_name
     , ( SELECT COUNT(*) 
         FROM userprofile_videoinfo vi
         WHERE u.id = vi.uploaded_by_id
       ) AS cnt_VideosUploadedBy                         <---- 5
    , cnt_Credits_InMyUploads 
        + cnt_Videos_CreditedIn - cnt_Videos_CreditedIn_and_UploadBy
      AS cnt_Difficult                               <---- 5 + 4 - 2 = 7
    , cnt_Credits_Total                                  <---- 8
    , cnt_Credits_InMyUploads                            <---- 5
    , cnt_Videos_CreditedIn                              <---- 4 
    , cnt_Videos_CreditedIn_and_UploadBy                 <---- 2
FROM userprofile_userprofile u 
  LEFT JOIN
      ( SELECT u.id
             , COUNT(vc.video_id)
               AS cnt_Credits_Total
             , (COUNT(vi.profile)
               AS cnt_Credits_InMyUploads
             , COUNT(DISTINCT vc.video_id)
               AS cnt_Videos_CreditedIn
             , (COUNT(DISTINCT vi.id)
               AS cnt_Videos_CreditedIn_and_UploadBy
        FROM userprofile_userprofile u 
          JOIN userprofile_videocredit vc
            ON u.id = vc.profile_by_id
          LEFT JOIN userprofile_videoinfo vi
            ON vc.video_id = vi.id
            AND vi.profile = u.id
        GROUP BY u.id
      ) AS grp
    ON grp.id = u.id