> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> YouTube 视频点踩数据集。

# YouTube 点踩数据集

2021 年 11 月，YouTube 移除了所有视频公开显示的 ***点踩*** 数。虽然创作者仍然可以看到点踩数量，但观众只能看到某个视频获得了多少 ***点赞***。

<Warning>
  该数据集包含超过 45.5 亿条记录，因此除非你的资源能够承载如此规模的数据量，否则请谨慎直接复制粘贴下面的命令。下面的命令是在 [ClickHouse Cloud](https://clickhouse.cloud) 的 **生产环境**实例上执行的。
</Warning>

这些数据采用 JSON 格式，可从 [archive.org](https://archive.org/download/dislikes_youtube_2021_12_video_json_files) 下载。我们也已将相同的数据提供在 S3 上，以便更高效地将其下载到 ClickHouse Cloud 实例中。

以下是在 ClickHouse Cloud 中创建表并插入数据的步骤。

<Note>
  下面的步骤同样也适用于本地安装的 ClickHouse。唯一需要修改的是使用 `s3` 函数而不是 `s3cluster` (除非你已经配置了集群——在这种情况下，将 `default` 改为你的集群名称) 。
</Note>

<div id="step-by-step-instructions">
  ## 分步指南
</div>

<Steps>
  <Step>
    ### 数据探索

    让我们看看这些数据的样子。`s3cluster` 表函数会返回一个表，因此我们可以对结果执行 `DESCRIBE`：

    ```sql theme={null}
    DESCRIBE s3(
        'https://clickhouse-public-datasets.s3.amazonaws.com/youtube/original/files/*.zst',
        'JSONLines'
    );
    ```

    ClickHouse 会从 JSON 文件中推断出以下 schema：

    ```response theme={null}
    ┌─name────────────────┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
    │ id                  │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ fetch_date          │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ upload_date         │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ title               │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ uploader_id         │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ uploader            │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ uploader_sub_count  │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
    │ is_age_limit        │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
    │ view_count          │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
    │ like_count          │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
    │ dislike_count       │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
    │ is_crawlable        │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
    │ is_live_content     │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
    │ has_subtitles       │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
    │ is_ads_enabled      │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
    │ is_comments_enabled │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
    │ description         │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ rich_metadata       │ Array(Tuple(call Nullable(String), content Nullable(String), subtitle Nullable(String), title Nullable(String), url Nullable(String))) │              │                    │         │                  │                │
    │ super_titles        │ Array(Tuple(text Nullable(String), url Nullable(String)))                                                                              │              │                    │         │                  │                │
    │ uploader_badges     │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    │ video_badges        │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
    └─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
    ```
  </Step>

  <Step>
    ### 创建表

    根据推断出的 schema，我们对数据类型进行了整理，并添加了主键。
    定义如下表：

    ```sql theme={null}
    CREATE TABLE youtube
    (
        `id` String,
        `fetch_date` DateTime,
        `upload_date_str` String,
        `upload_date` Date,
        `title` String,
        `uploader_id` String,
        `uploader` String,
        `uploader_sub_count` Int64,
        `is_age_limit` Bool,
        `view_count` Int64,
        `like_count` Int64,
        `dislike_count` Int64,
        `is_crawlable` Bool,
        `has_subtitles` Bool,
        `is_ads_enabled` Bool,
        `is_comments_enabled` Bool,
        `description` String,
        `rich_metadata` Array(Tuple(call String, content String, subtitle String, title String, url String)),
        `super_titles` Array(Tuple(text String, url String)),
        `uploader_badges` String,
        `video_badges` String
    )
    ENGINE = MergeTree
    ORDER BY (uploader, upload_date)
    ```
  </Step>

  <Step>
    ### 插入数据

    以下命令会将 S3 文件中的记录流式写入 `youtube` 表。

    <Warning>
      这会插入大量数据——46.5 亿行。如果你不需要整个数据集，只需添加一个 `LIMIT` 子句，并指定所需的行数。
    </Warning>

    ```sql theme={null}
    INSERT INTO youtube
    SETTINGS input_format_null_as_default = 1
    SELECT
        id,
        parseDateTimeBestEffortUSOrZero(toString(fetch_date)) AS fetch_date,
        upload_date AS upload_date_str,
        toDate(parseDateTimeBestEffortUSOrZero(upload_date::String)) AS upload_date,
        ifNull(title, '') AS title,
        uploader_id,
        ifNull(uploader, '') AS uploader,
        uploader_sub_count,
        is_age_limit,
        view_count,
        like_count,
        dislike_count,
        is_crawlable,
        has_subtitles,
        is_ads_enabled,
        is_comments_enabled,
        ifNull(description, '') AS description,
        rich_metadata,
        super_titles,
        ifNull(uploader_badges, '') AS uploader_badges,
        ifNull(video_badges, '') AS video_badges
    FROM s3(
        'https://clickhouse-public-datasets.s3.amazonaws.com/youtube/original/files/*.zst',
        'JSONLines'
    )
    ```

    关于我们的 `INSERT` 命令，有几点说明：

    * 当传入的日期字段格式可能不正确时，`parseDateTimeBestEffortUSOrZero` 函数会很有用。如果 `fetch_date` 无法被正确解析，就会被设为 `0`
    * `upload_date` 列中既有有效日期，也有像 "4 hours ago" 这样的字符串——这显然不是有效日期。我们决定将原始值存储在 `upload_date_str` 中，并尝试用 `toDate(parseDateTimeBestEffortUSOrZero(upload_date::String))` 对其进行解析。如果解析失败，结果就会是 `0`
    * 我们使用 `ifNull` 来避免表中出现 `NULL` 值。如果传入的值是 `NULL`，`ifNull` 函数会将其设为空字符串
  </Step>

  <Step>
    ### 统计行数

    在 ClickHouse Cloud 的 SQL 控制台中打开一个新标签页 (或新开一个 `clickhouse-client` 窗口) ，观察计数持续增加。
    插入 45.6 亿行需要一些时间，具体取决于服务器资源。 (如果不调整任何设置，大约需要 4.5 小时。)

    ```sql theme={null}
    SELECT formatReadableQuantity(count())
    FROM youtube
    ```

    ```response theme={null}
    ┌─formatReadableQuantity(count())─┐
    │ 4.56 billion                    │
    └─────────────────────────────────┘
    ```
  </Step>

  <Step>
    ### 探索数据

    数据插入后，不妨统计一下你喜欢的视频或频道被点踩的次数。让我们看看 ClickHouse 上传了多少个视频：

    ```sql theme={null}
    SELECT count()
    FROM youtube
    WHERE uploader = 'ClickHouse';
    ```

    ```response theme={null}
    ┌─count()─┐
    │      84 │
    └─────────┘

    1 row in set. Elapsed: 0.570 sec. Processed 237.57 thousand rows, 5.77 MB (416.54 thousand rows/s., 10.12 MB/s.)
    ```

    <Note>
      上面的查询之所以运行得这么快，是因为我们将 `uploader` 选作主键的第一列，因此只需处理 23.7 万行。
    </Note>

    下面来看看 ClickHouse 视频的点赞和点踩情况：

    ```sql theme={null}
    SELECT
        title,
        like_count,
        dislike_count
    FROM youtube
    WHERE uploader = 'ClickHouse'
    ORDER BY dislike_count DESC;
    ```

    响应如下：

    ```response theme={null}
    ┌─title────────────────────────────────────────────────────────────────────────────────────────────────┬─like_count─┬─dislike_count─┐
    │ ClickHouse v21.11 Release Webinar                                                                    │         52 │             3 │
    │ ClickHouse Introduction                                                                              │         97 │             3 │
    │ Casa Modelo Algarve                                                                                  │        180 │             3 │
    │ Профайлер запросов:  трудный путь                                                                    │         33 │             3 │
    │ ClickHouse в Курсометре                                                                              │          4 │             2 │
    │ 10 Good Reasons to Use ClickHouse                                                                    │         27 │             2 │
    ...

    84 rows in set. Elapsed: 0.013 sec. Processed 155.65 thousand rows, 16.94 MB (11.96 million rows/s., 1.30 GB/s.)
    ```

    以下是对 `title` 或 `description` 字段中包含 **ClickHouse** 的视频进行的搜索：

    ```sql theme={null}
    SELECT
        view_count,
        like_count,
        dislike_count,
        concat('https://youtu.be/', id) AS url,
        title
    FROM youtube
    WHERE (title ILIKE '%ClickHouse%') OR (description ILIKE '%ClickHouse%')
    ORDER BY
        like_count DESC,
        view_count DESC;
    ```

    该查询需要处理每一行数据，还要解析两列 String。即便如此，性能仍然相当不错，达到 415 万行/秒：

    ```response theme={null}
    1174 rows in set. Elapsed: 1099.368 sec. Processed 4.56 billion rows, 1.98 TB (4.15 million rows/s., 1.80 GB/s.)
    ```

    结果如下：

    ```response theme={null}
    ┌─view_count─┬─like_count─┬─dislike_count─┬─url──────────────────────────┬─title──────────────────────────────────────────────────────────────────────────────────────────────────┐
    │       1919 │         63 │             1 │ https://youtu.be/b9MeoOtAivQ │ ClickHouse v21.10 Release Webinar                                                                      │
    │       8710 │         62 │             4 │ https://youtu.be/PeV1mC2z--M │ What is JDBC DriverManager? | JDBC                                                                     │
    │       3534 │         62 │             1 │ https://youtu.be/8nWRhK9gw10 │ CLICKHOUSE - Arquitetura Modular                                                                       │
    ```
  </Step>
</Steps>

<div id="questions">
  ## 常见问题
</div>

<div id="if-someone-disables-comments-does-it-lower-the-chance-someone-will-actually-click-like-or-dislike">
  ### 如果有人关闭评论功能，是否会降低人们实际去点“点赞”或“点踩”的可能性？
</div>

关闭评论后，人们是否更倾向于通过点赞或点踩来表达自己对视频的感受？

```sql theme={null}
SELECT
    concat('< ', formatReadableQuantity(view_range)) AS views,
    is_comments_enabled,
    total_clicks / num_views AS prob_like_dislike
FROM
(
    SELECT
        is_comments_enabled,
        power(10, CEILING(log10(view_count + 1))) AS view_range,
        sum(like_count + dislike_count) AS total_clicks,
        sum(view_count) AS num_views
    FROM youtube
    GROUP BY
        view_range,
        is_comments_enabled
) WHERE view_range > 1
ORDER BY
    is_comments_enabled ASC,
    num_views ASC;
```

```response theme={null}
┌─views─────────────┬─is_comments_enabled─┬────prob_like_dislike─┐
│ < 10.00           │ false               │  0.08224180712685371 │
│ < 100.00          │ false               │  0.06346337759167248 │
│ < 1.00 thousand   │ false               │  0.03201883652987105 │
│ < 10.00 thousand  │ false               │  0.01716073540410903 │
│ < 10.00 billion   │ false               │ 0.004555639481829971 │
│ < 100.00 thousand │ false               │  0.01293351460515323 │
│ < 1.00 billion    │ false               │ 0.004761811192464957 │
│ < 1.00 million    │ false               │ 0.010472604018980551 │
│ < 10.00 million   │ false               │  0.00788902538420125 │
│ < 100.00 million  │ false               │  0.00579152804250582 │
│ < 10.00           │ true                │  0.09819517478134059 │
│ < 100.00          │ true                │  0.07403784478585775 │
│ < 1.00 thousand   │ true                │  0.03846294910067627 │
│ < 10.00 billion   │ true                │ 0.005615217329358215 │
│ < 10.00 thousand  │ true                │  0.02505881391701455 │
│ < 1.00 billion    │ true                │ 0.007434998802482997 │
│ < 100.00 thousand │ true                │ 0.022694648130822004 │
│ < 100.00 million  │ true                │ 0.011761563746575625 │
│ < 1.00 million    │ true                │ 0.020776022304589435 │
│ < 10.00 million   │ true                │ 0.016917095718089584 │
└───────────────────┴─────────────────────┴──────────────────────┘

22 rows in set. Elapsed: 8.460 sec. Processed 4.56 billion rows, 77.48 GB (538.73 million rows/s., 9.16 GB/s.)
```

启用评论似乎与更高的互动率有关。

<div id="how-does-the-number-of-videos-change-over-time---notable-events">
  ### 视频数量随时间如何变化——有哪些值得注意的事件？
</div>

```sql theme={null}
SELECT
    toStartOfMonth(toDateTime(upload_date)) AS month,
    uniq(uploader_id) AS uploaders,
    count() AS num_videos,
    sum(view_count) AS view_count
FROM youtube
GROUP BY month
ORDER BY month ASC;
```

```response theme={null}
┌──────month─┬─uploaders─┬─num_videos─┬───view_count─┐
│ 2005-04-01 │         5 │          6 │    213597737 │
│ 2005-05-01 │         6 │          9 │      2944005 │
│ 2005-06-01 │       165 │        351 │     18624981 │
│ 2005-07-01 │       395 │       1168 │     94164872 │
│ 2005-08-01 │      1171 │       3128 │    124540774 │
│ 2005-09-01 │      2418 │       5206 │    475536249 │
│ 2005-10-01 │      6750 │      13747 │    737593613 │
│ 2005-11-01 │     13706 │      28078 │   1896116976 │
│ 2005-12-01 │     24756 │      49885 │   2478418930 │
│ 2006-01-01 │     49992 │     100447 │   4532656581 │
│ 2006-02-01 │     67882 │     138485 │   5677516317 │
│ 2006-03-01 │    103358 │     212237 │   8430301366 │
│ 2006-04-01 │    114615 │     234174 │   9980760440 │
│ 2006-05-01 │    152682 │     332076 │  14129117212 │
│ 2006-06-01 │    193962 │     429538 │  17014143263 │
│ 2006-07-01 │    234401 │     530311 │  18721143410 │
│ 2006-08-01 │    281280 │     614128 │  20473502342 │
│ 2006-09-01 │    312434 │     679906 │  23158422265 │
│ 2006-10-01 │    404873 │     897590 │  27357846117 │
```

在[新冠疫情前后，上传者数量明显激增](https://www.theverge.com/2020/3/27/21197642/youtube-with-me-style-videos-views-coronavirus-cook-workout-study-home-beauty)。

<div id="more-subtitles-over-time-and-when">
  ### 字幕是从什么时候开始逐渐增多的
</div>

随着语音识别技术的进步，给视频制作字幕比以往任何时候都更容易。YouTube 在 2009 年底推出了自动字幕功能——字幕数量的增长是从那时开始的吗？

```sql theme={null}
SELECT
    toStartOfMonth(upload_date) AS month,
    countIf(has_subtitles) / count() AS percent_subtitles,
    percent_subtitles - any(percent_subtitles) OVER (
        ORDER BY month ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS previous
FROM youtube
GROUP BY month
ORDER BY month ASC;
```

```response theme={null}
┌──────month─┬───percent_subtitles─┬────────────────previous─┐
│ 2015-01-01 │  0.2652653881082824 │      0.2652653881082824 │
│ 2015-02-01 │  0.3147556050309162 │    0.049490216922633834 │
│ 2015-03-01 │ 0.32460464492371877 │    0.009849039892802558 │
│ 2015-04-01 │ 0.33471963051468445 │    0.010114985590965686 │
│ 2015-05-01 │  0.3168087575501062 │   -0.017910872964578273 │
│ 2015-06-01 │  0.3162609788438222 │  -0.0005477787062839745 │
│ 2015-07-01 │ 0.31828767677518033 │   0.0020266979313581235 │
│ 2015-08-01 │  0.3045551564286859 │   -0.013732520346494415 │
│ 2015-09-01 │   0.311221133995152 │    0.006665977566466086 │
│ 2015-10-01 │ 0.30574870926812175 │   -0.005472424727030245 │
│ 2015-11-01 │ 0.31125409712077234 │   0.0055053878526505895 │
│ 2015-12-01 │  0.3190967954651779 │    0.007842698344405541 │
│ 2016-01-01 │ 0.32636021432496176 │    0.007263418859783877 │

```

数据显示，2009 年出现了一个峰值。显然，当时 YouTube 正在取消其社区字幕功能，该功能允许用户为他人的视频上传字幕。
这促成了一场非常成功的倡议活动，推动创作者为自己的视频添加字幕，方便听障和失聪观众观看。

<div id="top-uploaders-over-time">
  ### 主要上传者随时间变化
</div>

```sql theme={null}
WITH uploaders AS
    (
        SELECT uploader
        FROM youtube
        GROUP BY uploader
        ORDER BY sum(view_count) DESC
        LIMIT 10
    )
SELECT
    month,
    uploader,
    sum(view_count) AS total_views,
    avg(dislike_count / like_count) AS like_to_dislike_ratio
FROM youtube
WHERE uploader IN (uploaders)
GROUP BY
    toStartOfMonth(upload_date) AS month,
    uploader
ORDER BY
    month ASC,
    total_views DESC;
```

```response theme={null}
┌──────month─┬─uploader───────────────────┬─total_views─┬─like_to_dislike_ratio─┐
│ 1970-01-01 │ T-Series                   │    10957099 │  0.022784656361208206 │
│ 1970-01-01 │ Ryan's World               │           0 │  0.003035559410234172 │
│ 1970-01-01 │ SET India                  │           0 │                   nan │
│ 2006-09-01 │ Cocomelon - Nursery Rhymes │   256406497 │    0.7005566715978622 │
│ 2007-06-01 │ Cocomelon - Nursery Rhymes │    33641320 │    0.7088650914344298 │
│ 2008-02-01 │ WWE                        │    43733469 │   0.07198856488734842 │
│ 2008-03-01 │ WWE                        │    16514541 │    0.1230603715431997 │
│ 2008-04-01 │ WWE                        │     5907295 │    0.2089399470159618 │
│ 2008-05-01 │ WWE                        │     7779627 │   0.09101676560436774 │
│ 2008-06-01 │ WWE                        │     7018780 │    0.0974184753155297 │
│ 2008-07-01 │ WWE                        │     4686447 │    0.1263845422065158 │
│ 2008-08-01 │ WWE                        │     4514312 │   0.08384574274791441 │
│ 2008-09-01 │ WWE                        │     3717092 │   0.07872802579349912 │
```

<div id="how-do-like-ratio-changes-as-views-go-up">
  ### 随着观看量增加，点赞率如何变化？
</div>

```sql theme={null}
SELECT
    concat('< ', formatReadableQuantity(view_range)) AS view_range,
    is_comments_enabled,
    round(like_ratio, 2) AS like_ratio
FROM
(
SELECT
    power(10, CEILING(log10(view_count + 1))) AS view_range,
    is_comments_enabled,
    avg(like_count / dislike_count) AS like_ratio
FROM youtube WHERE dislike_count > 0
GROUP BY
    view_range,
    is_comments_enabled HAVING view_range > 1
ORDER BY
    view_range ASC,
    is_comments_enabled ASC
);
```

```response theme={null}
┌─view_range────────┬─is_comments_enabled─┬─like_ratio─┐
│ < 10.00           │ false               │       0.66 │
│ < 10.00           │ true                │       0.66 │
│ < 100.00          │ false               │          3 │
│ < 100.00          │ true                │       3.95 │
│ < 1.00 thousand   │ false               │       8.45 │
│ < 1.00 thousand   │ true                │      13.07 │
│ < 10.00 thousand  │ false               │      18.57 │
│ < 10.00 thousand  │ true                │      30.92 │
│ < 100.00 thousand │ false               │      23.55 │
│ < 100.00 thousand │ true                │      42.13 │
│ < 1.00 million    │ false               │      19.23 │
│ < 1.00 million    │ true                │      37.86 │
│ < 10.00 million   │ false               │      12.13 │
│ < 10.00 million   │ true                │      30.72 │
│ < 100.00 million  │ false               │       6.67 │
│ < 100.00 million  │ true                │      23.32 │
│ < 1.00 billion    │ false               │       3.08 │
│ < 1.00 billion    │ true                │      20.69 │
│ < 10.00 billion   │ false               │       1.77 │
│ < 10.00 billion   │ true                │       19.5 │
└───────────────────┴─────────────────────┴────────────┘
```

<div id="how-are-views-distributed">
  ### 观看量是如何分布的？
</div>

```sql theme={null}
SELECT
    labels AS percentile,
    round(quantiles) AS views
FROM
(
    SELECT
        quantiles(0.999, 0.99, 0.95, 0.9, 0.8, 0.7, 0.6, 0.5, 0.4, 0.3, 0.2, 0.1)(view_count) AS quantiles,
        ['99.9th', '99th', '95th', '90th', '80th', '70th','60th', '50th', '40th', '30th', '20th', '10th'] AS labels
    FROM youtube
)
ARRAY JOIN
    quantiles,
    labels;
```

```response theme={null}
┌─percentile─┬───views─┐
│ 99.9th     │ 1216624 │
│ 99th       │  143519 │
│ 95th       │   13542 │
│ 90th       │    4054 │
│ 80th       │     950 │
│ 70th       │     363 │
│ 60th       │     177 │
│ 50th       │      97 │
│ 40th       │      57 │
│ 30th       │      32 │
│ 20th       │      16 │
│ 10th       │       6 │
└────────────┴─────────┘
```