我想使用 SQL 計算網站的幾篇文章的點擊率 (CTR)。
CTR的公式是CTR=點擊次數/展示次數，即一篇文章被點擊的次數與被展示的次數之比。
我有兩張桌子：
- 'article_click'：一個包含幾列的表格，即'article_id'（表示文章的id）、'description'（文章的簡要描述）、'timestamp'（點擊時）等。每次用戶單擊一篇文章時，都會在表中創建一個新行。
- 'article_impression'：類似地，一個有幾列的表，即'article_id'（表示文章的id）、'description'（文章的簡要描述）、'timestamp'（當它顯示時）等等. 每次向用戶顯示文章時，都會在表中創建一個新行。
表 1 和表 2 均如下所示：
------------ ------------- ------------------ -----
| article_id | description | timestamp | ... |
------------ ------------- ------------------ -----
| 102 | Potatoe | 2021-01-01 13:45 | ... |
| 11 | Lettuce | 2020-02-11 11:00 | ... |
| 34 | Train | 2019-12-12 09:31 | ... |
| 21 | Car | 2011-11-11 08:32 | ... |
| 201 | Train | 2014-02-10 02:12 | ... |
| ... | ... | ... | ... |
------------ ------------- ------------------ -----
我想創建一個表，這樣：
------------ -----
| article_id | CTR |
------------ -----
| 11 | 0.4 |
| 23 | 0.6 |
| 34 | 0.2 |
| 44 | 0.8 |
| 45 | 0.3 |
| ... | ... |
------------ -----
為此，我嘗試過：
SELECT article_click.article_id, COUNT(article_click.article_id) / COUNT(article_impression.article_id) AS CTR
FROM article_click
INNER JOIN article_impression ON article_click.article_id = article_impression.article_id
GROUP BY article_click.article_id DESC;
但我得到類似的東西：
------------ -----
| article_id | CTR |
------------ -----
| 11 | 1.0 |
| 23 | 1.0 |
| 34 | 1.0 |
| 44 | 1.0 |
| 45 | 1.0 |
| ... | ... |
------------ -----
誰能發現這里的錯誤？我使用 MySQL 作為 RDBMS。
uj5u.com熱心網友回復：
如果點擊率 (CTR) 是，number clicks / number impressions那么您需要先計算文章的點擊次數和文章的展示次數，然后再加入它們以執行計算。
您可以使用子查詢或 CTE 執行此操作，但我在這里選擇了前者。
SELECT c.article_id, c.click_count / i.impression_count AS CTR
FROM (
SELECT article_id, COUNT(*) AS click_count
FROM article_click
GROUP BY article_id) AS c
INNER JOIN (
SELECT article_id, COUNT(*) AS impression_count
FROM article_impression
GROUP BY article_id) AS i
ON c.article_id = i.article_id;
在 SQL Fiddle 上嘗試一下。
請注意，使用 anINNER JOIN將排除有展示但沒有點擊的文章，因此您不會獲得 CTR 為 0 的結果。如果您想要這些，您可以使用LEFT JOIN從展示次數到點擊次數的結果。由于一篇文章如果沒有顯示就無法點擊，我們知道LEFT JOIN從展示次數到點擊次數足以顯示所有資料。
SELECT i.article_id, COALESCE(c.click_count, 0) / i.impression_count AS CTR
FROM (
SELECT article_id, COUNT(*) AS impression_count
FROM article_impression
GROUP BY article_id) AS i
LEFT JOIN (
SELECT article_id, COUNT(*) AS click_count
FROM article_click
GROUP BY article_id) AS c
ON i.article_id = c.article_id;
請注意，我們必須使用article_idfrom article_impressionsince article_clickmight be NULL。出于同樣的原因，我們要COALESCE的click_count，這樣我們就不會試圖除法錯誤結束NULL。
uj5u.com熱心網友回復：
在使用連接之前，必須避免重復資料。獲取每個表的單獨計數并加入兩個查詢。
select a.article_id, article_click/article_impression_click as ctr
from ( select a.article_id, count(a.article_id) article_click from
article_click a group by article_id) a inner join (select
a.article_id, count(a.article_id) article_impression_click from
article_impression a group by article_id) b on
a.article_id=b.article_id
uj5u.com熱心網友回復：
WITH
v_article AS
( SELECT 'S' type, article_impression.id FROM article_impression
UNION ALL
SELECT 'C' type, article_click.id FROM article_click
)
SELECT
v_article.id,
COUNT(CASE WHEN v_article.type = 'C' THEN 1 END) nb_show,
COUNT(CASE WHEN v_article.type = 'S' THEN 1 END) nb_click,
CASE
WHEN COUNT(CASE WHEN v_article.type = 'S' THEN 1 END) > 0 THEN
ROUND(COUNT(CASE WHEN v_article.type = 'C' THEN 1 END) / COUNT(CASE WHEN v_article.type = 'S' THEN 1 END), 2)
END ratio_click_show
FROM v_article
GROUP BY
v_article.id
;
如果您確定只有在之前顯示過的文章才能點擊（nb_show > 0 和 nb_show > nb_click），您可以洗掉比率計算周圍的 CASE。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/407725.html
標籤：