我正在嘗試撰寫一個 SQL 查詢，以降序顯示課程受歡迎程度。
- 課程受歡迎程度以點數衡量，點數確定如下：對于每項調查：
- 一種。如果票數差異>總票數的 10%，則較受歡迎的課程得 1 分，較不受歡迎的課程得 0 分
- 灣。如果票數差異 <= 總票數的 10%，每門課程得 0.5 分
| course_id | 課程名 | 學院 |
|---|---|---|
| 1001 | 經濟學_101 | 商業 |
| 1002 | 代數_101 | 數學 |
| 1003 | 幾何_101 | 數學 |
| 1004 | 管理_101 | 商業 |
| 1005 | 營銷_101 | 商業 |
| 1006 | 物理_101 | 科學 |
| 調查ID | option_a | option_b | 投票_a | 投票_b |
|---|---|---|---|---|
| 2001年 | 經濟學_101 | 幾何_101 | 61 | 34 |
| 2002年 | 代數_101 | 經濟學_101 | 31 | 68 |
| 2003年 | 營銷_101 | 管理_101 | 11 | 72 |
| 2005年 | 管理_101 | 代數_101 | 43 | 54 |
| 2004年 | 幾何_101 | 營銷_101 | 48 | 46 |
目前取得的成果：
| 課程 | 人氣 |
|---|---|
| 經濟學_101 | 4 |
| 管理_101 | 2 |
| 代數_101 | 2 |
| 營銷_101 | 1 |
| 幾何_101 | 1 |
| [空值] | 0 |
到目前為止，我設法加入了它，如果能在優化此查詢方面提供意見，那就太好了：
WITH x AS
(
WITH b AS
(
WITH a as
(
select * from course c
LEFT JOIN survey s
on c.course_name = s.option_a
UNION ALL
select * from course c
LEFT JOIN survey s
on c.course_name = s.option_b
)
SELECT a.*,
SUM(votes_a votes_b) as total_votes,
CASE WHEN (a.votes_a - a.votes_b) > (0.1*SUM(votes_a votes_b)) THEN 1
WHEN (a.votes_b - a.votes_a) <= (0.1*SUM(votes_a votes_b)) THEN 0.5
ELSE 0
END AS 'Popularity_a',
CASE WHEN (a.votes_b - a.votes_a) > (0.1*SUM(votes_a votes_b)) THEN 1
WHEN (a.votes_a - a.votes_b) <= (0.1*SUM(votes_a votes_b)) THEN 0.5
ELSE 0
END AS 'Popularity_b'
FROM
a
GROUP BY
a.course_name ,
a.course_id,
a.faculty ,
a.survey_id ,
a.option_a ,
a.option_b ,
a.votes_a ,
a.votes_b
)
SELECT b.option_a as course,
b.Popularity_a as pop
FROM b
LEFT JOIN
course cx
ON b.option_a = cx.course_name
UNION ALL
SELECT b.option_b as course ,
b.Popularity_b as pop
FROM b
LEFT JOIN
course cx
ON b.option_b = cx.course_name
)
select
x.course ,
sum (x.pop) as popularity
from x
GROUP BY
x.course
order by popularity desc
uj5u.com熱心網友回復：
用于UNION ALL提取所有課程和他們從表中獲得的相應分數survey并聚合以獲得popularity.
然后加入course：
WITH
cte AS (
SELECT option_a course_name,
CASE
WHEN votes_a - votes_b > 0.1 * (votes_a votes_b) THEN 1.0
WHEN votes_b - votes_a > 0.1 * (votes_a votes_b) THEN 0.0
ELSE 0.5
END points
FROM survey
UNION ALL
SELECT option_b,
CASE
WHEN votes_b - votes_a > 0.1 * (votes_a votes_b) THEN 1.0
WHEN votes_a - votes_b > 0.1 * (votes_a votes_b) THEN 0.0
ELSE 0.5
END
FROM survey
),
points AS (
SELECT course_name, SUM(points) total_points
FROM cte
GROUP BY course_name
)
SELECT c.*, COALESCE(p.total_points, 0) popularity
FROM course c LEFT JOIN points p
ON p.course_name = c.course_name
ORDER BY popularity DESC;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/446889.html
標籤：sql sqlite 通过...分组 案子 sql-order-by