問題描述
自聯接以比較同一張表中的季度數據？ (Self join to compare quarterly data from same table?)
I'm helping a small rural health system add some databanking to their operations. They currently keep track of some operational statistics that they report each quarter.
I'd like to put the meat of these responses in one table and do historical comparisons from it. I'll have some additional tables for normalization, but here's the main one.
+-------+----------+-------------+----------+
| year | quarter | question_id | response |
+-------+----------+-------------+----------+
| 2000 | 1 | 100 | 50 |
+-------+----------+-------------+----------+
| 2000 | 2 | 100 | 100 |
+-------+----------+-------------+----------+
| 2000 | 1 | 200 | 75 |
+-------+----------+-------------+----------+
| 2000 | 2 | 200 | 25 |
+-------+----------+-------------+----------+
The output I'm looking for is to put those quarterly responses for each question side-by-side so I can calculate changes better and easy up my PHP processing. Below is the desired output:
+-------------+----------------------+----------------------+
| question_id | 1st quarter response | 2nd quarter response |
+-------------+----------------------+----------------------+
| 100 | 50 | 100 |
+-------------+----------------------+----------------------+
| 200 | 75 | 25 |
+-------------+----------------------+----------------------+
I'm new to SQL (using MySQL 5) and the only thing I can think of is I need a self join. I've tried but can't seem to get it. Am I approaching the table structure the correct way for this kind of historical analysis?
Any pointers would be a big help and a help to our project!
Thanks!
參考解法
方法 1:
You could group by question, and then use a combination of case and max to select the answer for that quarter.
select question_id
, max(case when quarter = 1 then question_responses end) as Q1
, max(case when quarter = 2 then question_responses end) as Q2
, max(case when quarter = 3 then question_responses end) as Q3
, max(case when quarter = 4 then question_responses end) as Q4
from question_responses
where year = 2000
group by
question_id
In this example the max doesn't actually max anything, it ends up selecting the only quarter for which the case returns a value.
方法 2:
Well assuming you want to get that output for a specific year, eg. 2000:
SELECT
question_id,
(SELECT response FROM question_responses WHERE year = qr.year AND question_id = qr.question_id AND quarter = 1) AS qrt_resp_1,
(SELECT response FROM question_responses WHERE year = qr.year AND question_id = qr.question_id AND quarter = 2) AS qrt_resp_2
FROM question_responses AS qr
WHERE year = 2000
GROUP BY question_id