SQL筆記

SELECT 
	f.title,
    r.imdb_score,
    LAG(r.imdb_score,1) OVER(ORDER BY r.imdb_score DESC) --前一個分數較高的作品   
FROM cinema.reviews AS r
JOIN cinema.films AS f
ON r.id = f.id
WHERE r.imdb_score IS NOT NULL -- 有未被評分的電影
ORDER BY r.imdb_score DESC
LIMIT 10;

-- 這裡是怪異的 Pivoting 轉置功能註解
-- 下方是轉置的啟動語句，意思是說如果沒有轉置的功能要利用EXTENSION的功能
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 將要轉換的查詢利用 CROSSTAB 並用($$   $$)來包住內容，而這部分應該是指啟用 EXTENSION中的CROSSTAB
SELECT * FROM CROSSTAB($$
-- 這裡選用CTE先建立一個查詢，以便後續欄位與值更好篩選                       
  WITH Country_Awards AS (
    SELECT
      Country,
      Year,
      COUNT(*) AS Awards
    FROM Summer_Medals
    WHERE
      Country IN ('FRA', 'GBR', 'GER')
      AND Year IN (2004, 2008, 2012)
      AND Medal = 'Gold'
    GROUP BY Country, Year)
-- 進入主查詢
  SELECT
    Country,
    Year,
    RANK() OVER
      (PARTITION BY Year
       ORDER BY Awards DESC) :: INTEGER AS rank
  FROM Country_Awards
  ORDER BY Country ASC, Year ASC;
-- 這裡以上都是包含在 $$)中被轉換的主要內容
-- Fill in the correct column names for the pivoted table
$$) AS ct 

/*
是否綁定一定要AS為ct不確定，但下方是呈現需要轉置的欄位將呈現什麼樣子
上方查詢得到的資料是以國家、年度、排名的一張表格如下一個區塊
*/

(Country VARCHAR,
"2004" INTEGER,
"2008" INTEGER,
"2012" INTEGER)

Order by Country ASC;

-- 版本一，可以獲得每個國家的獎牌種類與數量
-- Count the medals per gender and medal type
SELECT
  country,
  gender,
  medal AS Medal_type,
  COUNT(*) AS Awards
FROM medals.Summer_Medals
WHERE
  Year = 2012
  AND Country = 'RUS'
-- Get all possible group-level subtotals
GROUP BY country, Gender, medal
ORDER BY Gender ASC, Medal ASC;