『SQLパズル 第2版』の別解など その2

『SQLパズル 第2版』の別解など その1 - 不思議の備忘録

の続き

パズル27

■答え２を正しく動作するように改良

SELECT S1.sno, S2.sno
  FROM Supparts AS S1 , Supparts AS S2
 WHERE S1.sno < S2.sno
   AND S1.pno IN (SELECT S22.pno
                    FROM Supparts AS S22
                   WHERE S22.sno = S2.sno)
   AND S2.pno IN (SELECT S11.pno
                    FROM Supparts AS S11
                   WHERE S11.sno = S1.sno)
 GROUP BY S1.sno, S2.sno
HAVING COUNT(*) = (SELECT COUNT(*)
                     FROM Supparts AS S3
                    WHERE S3.sno = S1.sno)
                  *
                  (SELECT COUNT(*)
                     FROM Supparts AS S4
                    WHERE S4.sno = S2.sno);

答え５の"COUNT(SP1.sno || SP2.sno)"は"COUNT(CASE WHEN SP1.sno = SP2.sno THEN 1 ELSE NULL END)" に置き換えるとうまくいかない

パズル29

答え4について

 check_amt | cnt
-----------+-----
    100.00 |   3
    300.00 |   3

のように表示したい

SELECT DISTINCT check_amt, cnt
  FROM (SELECT check_amt
              ,cnt
              ,MAX(cnt) OVER() AS maxcnt
          FROM (SELECT check_amt
                      ,COUNT(*) OVER(PARTITION BY check_amt) AS cnt
                  FROM Payroll) AS TMP) AS TMP2
  WHERE cnt = maxcnt;

WHERE句でWindow関数やSELECT句のエイリアスを使えたらネストが減るのになあ

パズル30

■Window関数を使った別解

SELECT customer_name 
      ,AVG(gap)
  FROM (SELECT customer_name
              ,sale_date - MAX(sale_date) OVER(PARTITION BY customer_name ORDER BY sale_date 
                                               ROWS BETWEEN 1 PRECEDING AND 1PRECEDING) AS gap
          FROM Sales) AS TMP
 GROUP BY customer_name;

パズル31

1つのクエリで全部買った顧客、一部買った顧客、1つも買ってない顧客の売掛金残高の平均を出す

SELECT customer_type
      ,AVG(balance)
  FROM (SELECT C.customer_id
              ,CASE COUNT(DISTINCT item_id) WHEN (SELECT COUNT(DISTINCT item_id) FROM Products) THEN '全部買った'
                                            WHEN 0 THEN '何も買ってない'
                                            ELSE '一部買った' END AS customer_type
              ,MAX(acct_balance) AS balance
          FROM Customers C 
               LEFT OUTER JOIN
                  (Orders O INNER JOIN OrderDetails OD
                       ON O.order_id = OD.order_id)
                 ON O.customer_id = C.customer_id
        GROUP BY C.customer_id) AS TMP
 GROUP BY customer_type;

パズル32

答え３について、結合でＴ２のほうを使っていて個人的には以下のようにスカラサブクエリを使ったほうが直感的にわかりやすい気がする

SELECT SUM(tax_rate) AS total_taxes
  FROM TaxRates AS T1
 WHERE (SELECT DISTINCT lft FROM TaxRates AS T2 WHERE T2.tax_authority = 'city2') BETWEEN T1.lft AND T1.rgt
   AND '1994-11-01' BETWEEN T1.start_date AND COALESCE(T1.end_date, CURRENT_DATE);

パズル33

■Window関数を使った別解

Window関数の中に集約関数を入れ子にすることができる

SELECT machine_name
      ,manu_date
      ,(accum_cost + accum_depreciation) / accum_hrs AS total_cost_per_hour
  FROM (SELECT M.machine_name
              ,manu_date
              ,(manu_date + 1 - M.purchase_date) * (initial_cost / lifespan) AS accum_depreciation
              ,SUM(SUM(manu_hrs)) OVER(PARTITION BY M.machine_name ORDER BY manu_date) AS accum_hrs
              ,SUM(SUM(manu_cost)) OVER(PARTITION BY M.machine_name ORDER BY manu_date) AS accum_cost 
          FROM ManufactHrsCosts AS MHC
               INNER JOIN 
               Machines AS M
               ON M.machine_name = MHC.machine_name
         GROUP BY M.machine_name, manu_date) AS TMP;

SELECT M.machine_name
      ,manu_date
      ,(
        (manu_date + 1 - M.purchase_date) * (initial_cost / lifespan)
         + 
        SUM(SUM(manu_cost)) OVER(PARTITION BY M.machine_name ORDER BY manu_date)
       )  
       /  
       SUM(SUM(manu_hrs)) OVER(PARTITION BY M.machine_name ORDER BY manu_date) AS total_cost_per_hour
  FROM ManufactHrsCosts AS MHC
       INNER JOIN 
       Machines AS M
       ON M.machine_name = MHC.machine_name
 GROUP BY M.machine_name, manu_date;

パズル34

スカラサブクエリがスカラになる保証がないんじゃないかと思っていましたが

SELECT C1.emp_id, C1.emp_name, 
       SUM (bill_hrs * (SELECT bill_rate 
                          FROM Billings AS B1 
                         WHERE bill_date = (SELECT MAX(bill_date)
                                            FROM Billings AS B2
                                           WHERE B2.bill_date <= H1.work_date
                                             AND B1.emp_id = B2.emp_id
                                             AND B1.emp_id = H1.emp_id)))
  FROM HoursWorked AS H1, Consultants AS C1
 WHERE H1.emp_id = C1.emp_id
 GROUP BY C1.emp_id, C1.emp_name;

SELECT bill_rate 
  FROM Billings AS B1 
 WHERE bill_date = (SELECT MAX(bill_date)
                      FROM Billings AS B2
                     WHERE B2.bill_date <= '1990-07-01'
                       AND B1.emp_id = B2.emp_id
                       AND B1.emp_id = 3);

一番最後の行でMAX(bill_date)がnullになって外側のWHEREが通らないためスカラであることが保証されますね

あと答え2の最後の条件は不要なのではないでしょうか

SELECT emp_name, SUM(H1.bill_hrs * B1.bill_rate)
  FROM Consultants AS C1, Billings AS B1, Hoursworked AS H1
 WHERE C1.emp_id = B1.emp_id
   AND C1.emp_id = H1.emp_id
   AND bill_date = (SELECT MAX(bill_date)
                      FROM Billings AS B2
                     WHERE B2.emp_id = C1.emp_id
                       AND B2.bill_date <= H1.work_date)
   --AND H1.work_date >= bill_date
 GROUP BY emp_name;

パズル41

これについても一番外側の集約は要らないのではないでしょうか

SELECT I1.item_nbr, item_descr, actual_tot, estimated_tot, check_nbr
  FROM ITEMS AS I1
       LEFT OUTER JOIN 
       (SELECT item_nbr,
               SUM(actual_amt) AS actual_tot,
               CASE COUNT(check_nbr) WHEN 0 THEN NULL
                                     WHEN 1 THEN MAX(check_nbr)
                                     ELSE 'Mixed' END AS check_nbr
          FROM Actuals
         GROUP BY item_nbr) AS A1
       ON I1.item_nbr = A1.item_nbr
       LEFT OUTER JOIN
       (SELECT item_nbr,
               SUM(estimated_amt) AS estimated_tot 
          FROM Estimates
         GROUP BY item_nbr) AS E1
       ON I1.item_nbr = E1.item_nbr
 WHERE actual_tot IS NOT NULL
    OR estimated_tot IS NOT NULL; 

パズル44

答え1は同一ペアが2重にカウントされてしまうので正しくない。さらにちょっとした修正を加えたものが以下

SELECT S0.item_a
      ,S0.item_b
      ,CASE WHEN S0.item_a = S0.item_b
            THEN SUM(S0.pair_tally + S1.pair_tally) / 2 
            ELSE SUM(S0.pair_tally + S1.pair_tally) END AS pair_tally
  FROM SalesSlips AS S0, SalesSlips AS S1
 WHERE S0.item_a <= S0.item_b
   AND S0.item_a = S1.item_b
   AND S0.item_b = S1.item_a
 GROUP BY S0.item_a, S0.item_b, S1.item_a, S1.item_b;