用 Big Query 检查 tzfpy 下载情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#standardSQL
SELECT
  DATE(timestamp) as download_date,
  COUNT(*) as daily_downloads
FROM
  `bigquery-public-data.pypi.file_downloads`
WHERE
  file.project = 'tzfpy'
  AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 45 DAY) AND CURRENT_DATE()
GROUP BY
  download_date
ORDER BY
  download_date;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#standardSQL
SELECT
  country_code,
  COUNT(*) as download_count
FROM
  `bigquery-public-data.pypi.file_downloads`
WHERE
  file.project = 'tzfpy'
  AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE()
GROUP BY
  country_code
ORDER BY
  download_count DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

#standardSQL
SELECT
  details.distro.name AS distribution,
  details.distro.version AS distro_version,
  COUNT(*) AS download_count
FROM
  `bigquery-public-data.pypi.file_downloads`
WHERE
  file.project = 'tzfpy'
  AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE()
  AND details.distro.name IS NOT NULL
GROUP BY
  distribution, distro_version
ORDER BY
  download_count DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

#standardSQL
WITH Period1 AS (
  SELECT
    details.distro.name AS distribution,
    COUNT(*) AS downloads_period1
  FROM
    `bigquery-public-data.pypi.file_downloads`
  WHERE
    file.project = 'tzfpy'
    AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 15 DAY)
    AND details.distro.name IS NOT NULL
  GROUP BY
    distribution
),
Period2 AS (
  SELECT
    details.distro.name AS distribution,
    COUNT(*) AS downloads_period2
  FROM
    `bigquery-public-data.pypi.file_downloads`
  WHERE
    file.project = 'tzfpy'
    AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE()
    AND details.distro.name IS NOT NULL
  GROUP BY
    distribution
)
SELECT
  p1.distribution,
  p1.downloads_period1,
  IFNULL(p2.downloads_period2, 0) AS downloads_period2,
  IFNULL(p2.downloads_period2, 0) - p1.downloads_period1 AS change_in_downloads
FROM
  Period1 p1
LEFT JOIN
  Period2 p2
ON
  p1.distribution = p2.distribution
ORDER BY
  change_in_downloads DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

#standardSQL
WITH Period1 AS (
  SELECT
    country_code,
    COUNT(*) AS downloads_period1
  FROM
    `bigquery-public-data.pypi.file_downloads`
  WHERE
    file.project = 'tzfpy'
    AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 15 DAY)
  GROUP BY
    country_code
),
Period2 AS (
  SELECT
    country_code,
    COUNT(*) AS downloads_period2
  FROM
    `bigquery-public-data.pypi.file_downloads`
  WHERE
    file.project = 'tzfpy'
    AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE()
  GROUP BY
    country_code
)
SELECT
  p1.country_code,
  p1.downloads_period1,
  IFNULL(p2.downloads_period2, 0) AS downloads_period2,
  IFNULL(p2.downloads_period2, 0) - p1.downloads_period1 AS change_in_downloads
FROM
  Period1 p1
LEFT JOIN
  Period2 p2
ON
  p1.country_code = p2.country_code
ORDER BY
  change_in_downloads DESC;