整理SQL查詢

對于一個練習專案，我撰寫了以下查詢，我想知道是否有辦法讓它比撰寫所有內容更有效，比如為 sql 的 for 回圈撰寫 12 次。

CREATE TABLE temp (month INT, total_sales INT, market_share decimal(5,2), year_change decimal(5,2))

insert into temp (month)
Values (1)

UPDATE temp
SET total_sales = (
    SELECT COUNT(purchases_2020.purchaseid)
    FROM purchases_2020
    JOIN categories ON purchases_2020.purchaseid = categories.purchase_id
    WHERE (categories.category = 'whole milk' OR categories.category = 'yogurt' OR categories.category = 'domestic eggs') AND (purchases_2020.fulldate BETWEEN '2020-01-01' AND '2020-01-31')
) 
WHERE month = 1

UPDATE temp
SET market_share = (
        SELECT (SELECT 100 * COUNT(purchases_2020.purchaseid)
                FROM purchases_2020
                JOIN categories ON purchases_2020.purchaseid = categories.purchase_id
                WHERE (categories.category = 'whole milk' OR categories.category = 'yogurt' OR categories.category = 'domestic eggs') AND (purchases_2020.fulldate BETWEEN '2020-01-01' AND '2020-01-31'))
       * 1. /  
               (SELECT COUNT(purchases_2020.purchaseid) 
               FROM purchases_2020
               WHERE purchases_2020.fulldate BETWEEN '2020-01-01' AND '2020-01-31')
) 
WHERE month = 1

UPDATE temp
SET year_change = (
    SELECT market_share - 
        (SELECT 
            (SELECT 100 * COUNT(purchases_2019.purchase_id)
            FROM purchases_2019
            JOIN categories ON purchases_2019.purchase_id = categories.purchase_id
            WHERE (categories.category = 'whole milk' OR categories.category = 'yogurt' OR categories.category = 'domestic eggs') AND (purchases_2019.full_date BETWEEN '2019-01-01' AND '2019-01-31'))
        * 1./  
            (SELECT COUNT(purchases_2019.purchase_id) 
            FROM purchases_2019
            WHERE purchases_2019.full_date BETWEEN '2019-01-01' AND '2019-01-31'))
    FROM temp
    WHERE month = 1
) 
WHERE month = 1

編輯

我得到了以下資料庫架構中表示的 3 個表，我試圖創建一個表，其中包含每月乳制品的總銷售額、乳制品的月市場份額以及 2020 年月度市場份額與 2019 年月度市場份額之間的差異市場份額（年變化欄）

某處還有一個算術錯誤，在檢查專案時我收到以下訊息ResultSet 不包含正確的數值！我在尋找它，但我的首要任務是消除查詢。

uj5u.com熱心網友回復：

您的錯誤訊息告訴我您正在嘗試從報告工具或宿主語言運行它。

按年份將資料放入單獨的表中也是沒有意義的。

SQL 是一種以集合形式處理資料的宣告性語言。

不要將結果推送到 table 中temp，而是嘗試撰寫如下查詢：

with all_data as (
  select p.fulldate, p.purchaseid, c.category,
         extract(year  from p.fulldate) as year,
         extract(month from p.fulldate) as month
    from purchases_2020 p
         join categories c on c.purchase_id = p.purchaseid
  union all
  select p.fulldate, p.purchaseid, c.category,
         extract(year  from p.fulldate) as year,
         extract(month from p.fulldate) as month
    from purchases_2019 p
         join categories c on c.purchase_id = p.purchaseid
), kpis as (
  select year, month, 
         count(purchaseid) 
           filter (where category in ('whole milk', 'yogurt', 'domestic eggs'))
             as dairy_sales,
         count(purchaseid) * 1.0 as total_sales
    from all_data
)
select ty.month, ty.dairy_sales as total_sales,
       100.0 * ty.dairy_sales / ty.total_sales as market_share,
       100.0 * (   (ty.dairy_sales / ty.total_sales) 
                 - (ly.dairy_sales / ly.total_sales)) as year_change
  from kpis ty
       join kpis ly
         on (ly.year, ly.month) = (ty.year - 1, ty.month);

標籤：sql PostgreSQL

上一篇：PSQL從檔案執行命令，其中檔案是根據傳遞的變數確定的

下一篇：postgres/psycopg：匯入CSV的日期格式錯誤