如何以编程方式获取月度 pageviews？最好使用 Data Explorer

[Konrad_Sopala]

嘿，社区的朋友们！

请问有人写过使用数据资源管理器（Data Explorer）来获取月度页面浏览量的查询吗？

除了手动访问我的仪表板，还有其他编程方式可以获取这些数字吗？

[michebs (Michelle)]

这是 Consolidated Pageviews 报告背后的查询，希望对您有帮助。

-- [参数]
-- date :start_date
-- date :end_date

SELECT
    ar.date,
    CASE 
      WHEN ar.req_type=6 THEN '爬虫'
      WHEN ar.req_type=7 THEN '已登录用户'
      WHEN ar.req_type=8 THEN '匿名用户'
    END,
    ar.count AS views
FROM application_requests ar
WHERE req_type IN (6,7,8)
    AND ar.date::date BETWEEN :start_date::date
    AND :end_date::date
ORDER BY ar.date ASC, ar.req_type

[Konrad_Sopala]

太好了！这有点帮助！

能否获取这个数字：

[michebs (Michelle)]

此查询列出了该时间段内的页面浏览量总数。

-- [params]
-- date :start_date
-- date :end_date

WITH data AS (
    SELECT
        ar.date,
        CASE 
          WHEN ar.req_type=6 THEN '爬虫'
          WHEN ar.req_type=7 THEN '已登录用户'
          WHEN ar.req_type=8 THEN '匿名用户'
        END AS Pageview,
        ar.count AS views
    FROM application_requests ar
    WHERE req_type IN (6,7,8)
        AND ar.date::date BETWEEN :start_date::date
    AND :end_date::date
    ORDER BY ar.date ASC, ar.req_type
)

SELECT Pageview, SUM(views) qtt_views
FROM data
GROUP BY Pageview

[Konrad_Sopala]

完美！这正是我在找的。

[Konrad_Sopala]

其实还有一个问题。这可能不算太麻烦。

有没有办法使用 sum 获取如下格式的结果：

[michebs (Michelle)]

是的，这是可行的。在这种情况下，您需要先调整日期字段的格式，然后再调整分组方式。以下是调整后的查询语句。

-- [params]
-- date :start_date
-- date :end_date

WITH data AS 
    (SELECT
        date_part('month', ar.date) AS month,
        date_part('year', ar.date) AS year,
        CASE 
          WHEN ar.req_type=6 THEN 'Crawlers'
          WHEN ar.req_type=7 THEN 'Logged in users'
          WHEN ar.req_type=8 THEN 'Anonymous users'
        END AS pageview,
        ar.count AS views
    FROM application_requests ar
    WHERE req_type IN (6,7,8)
        AND ar.date::date BETWEEN :start_date::date
    AND :end_date::date
    ORDER BY ar.date ASC, ar.req_type
    )
    
  SELECT 
    month, 
    year, 
    sum(views) AS qtt_views
  FROM  data 
  GROUP BY year, month
  ORDER BY year DESC, month ASC

month	year	count
1	2021	1000
2	2021	500
1	2020	1500
2	2020	2000
3	2020	2500

[Konrad_Sopala]

完美！谢谢你的帮助！