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

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

の続き

パズル62

SQLメモ - 不思議の備忘録の「二人組作って～」のN列に一般化 ――３列以上に拡張したい場合もWindow関数ならスッキリ

--4列の場合
SELECT MAX(CASE col WHEN 1 THEN name END) AS name1
      ,MAX(CASE col WHEN 2 THEN name END) AS name2
      ,MAX(CASE col WHEN 3 THEN name END) AS name3
      ,MAX(CASE col WHEN 4 THEN name END) AS name4
  FROM (SELECT name
              ,(ROW_NUMBER() OVER(ORDER BY name) - 1) / 4 + 1 AS row
              ,(ROW_NUMBER() OVER(ORDER BY name) - 1) % 4 + 1 AS col
          FROM Names) AS TMP
 GROUP BY row
 ORDER BY name1, name2, name3, name4;

パズル63

■Window関数を用いた別解

SELECT MIN(num) AS low, MAX(num) AS high, MAX(data) AS data
  FROM (SELECT num
              ,data
              ,SUM(changed) OVER(ORDER BY num) AS group_n
          FROM (SELECT num
                      ,data
                      ,CASE WHEN MAX(data) OVER(ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = data THEN 0 
                            ELSE 1 END AS changed
                  FROM T
               ) AS TMP
       ) AS TMP2
 GROUP BY group_n
 ORDER BY group_n;

GROUP BY句でWindow関数が使えればネストが1段減るんだけどなあ… Window関数の中に同じか一部を含むフレーム定義のWindow関数を入れられるようになったらいいなあ…

パズル65

ミック氏のサポートページhttp://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.htmlの演習問題のSQL

SELECT product_id
  FROM PriceByAge
 WHERE NOT (low_age > 55 OR high_age < 10)
 GROUP BY product_id
HAVING SUM(CASE WHEN high_age < 55 THEN high_age ELSE 55 END - CASE WHEN low_age > 10 THEN low_age ELSE 10 END + 1) = 55 - 10 + 1;

パズル69

■Window関数を使った答え２の別解(LIFOのほう)

SELECT SUM(picked_qty * unit_price) AS total_cost
  FROM (SELECT CASE WHEN SUM(qty_on_hand) OVER(ORDER BY purchase_date DESC) <= 100
                    THEN qty_on_hand
                    WHEN SUM(qty_on_hand) OVER(ORDER BY purchase_date DESC) - qty_on_hand <= 100
                    THEN SUM(qty_on_hand) OVER(ORDER BY purchase_date DESC) - 100
                    ELSE 0
                    END AS picked_qty
              ,unit_price
          FROM WidgetInventory) AS TMP
 WHERE picked_qty > 0;

(演習問題)要求された個数以下で最も近い部品数の組み合わせ

WHERE total_pick = (SELECT MAX(total_pick)
                      FROM PickCombos
                     WHERE total_pick <= goal_qty)

パズル71

答え２や３をみるにSELECT句でSUMを使ってるあたりpin_nbrは重複を認めているんじゃないかなと思いました。 つまり主キーが一切ないテーブルになります

CREATE TABLE Foobaz
(empl_id CHAR(6) NOT NULL, pin_nbr CHAR(5) NOT NULL,
 empl_rcd INTEGER NOT NULL, calc_rslt_val INTEGER NOT NULL,
 calc_adj_val INTEGER NOT NULL, unit_rslt_val INTEGER NOT NULL,
 unit_adj_val INTEGER NOT NULL);

INSERT INTO Foobaz VALUES('xxxxxx', '52636', 0, 10, 20, 30, 40);
INSERT INTO Foobaz VALUES('xxxxxx', '52751', 0,  5,  6,  7,  8);
INSERT INTO Foobaz VALUES('xxxxxx', '52768', 0, 20, 40, 60, 80);
INSERT INTO Foobaz VALUES('xxxxxx', '52636', 0, 10, 20, 30, 40);
INSERT INTO Foobaz VALUES('xxxxxx', '52751', 0,  5,  6,  7,  8);
INSERT INTO Foobaz VALUES('xxxxxx', '52768', 0, 20, 40, 60, 80);

この場合答えその２とその３は違うクエリ結果になります（列持ち行もちの違いではなく）

SELECT DISTINCT
         SUM(F1.calc_rslt_val + F1.calc_adj_val) AS calc_1,
         SUM(F1.unit_rslt_val + F1.unit_adj_val) AS unit_1,
         SUM(f2.calc_rslt_val + f2.calc_adj_val) AS calc_2,
         SUM(f2.unit_rslt_val + f2.unit_adj_val) AS unit_2,
         SUM(f3.calc_rslt_val + f3.calc_adj_val) AS calc_3,
         SUM(f3.unit_rslt_val + f3.unit_adj_val) AS unit_3
  FROM Foobaz F1, Foobaz f2, Foobaz f3
 WHERE F1.empl_id = 'xxxxxx'
   AND f2.empl_id = 'xxxxxx'
   AND f3.empl_id = 'xxxxxx'
   AND F1.empl_rcd = 0
   AND F1.pin_nbr = '52636'
   AND f2.pin_nbr = '52751'
   AND f3.pin_nbr = '52768';

SELECT F1.pin_nbr,
       SUM(F1.calc_rslt_val + F1.calc_adj_val) AS calc_val,
       SUM(F1.unit_rslt_val + F1.unit_adj_val) AS unit_val
  FROM Foobaz F1
 WHERE F1.empl_id = 'xxxxxx'
   AND F1.empl_rcd = 0
   AND F1.pin_nbr IN ('52636', '52751', '52768')
 GROUP BY F1.pin_nbr;

結合条件で'52636'は２つあるのでAA,AB,BA,BBの重複が発生します

パズル73

演習問題：ORでつながずに「少なくとも1列は9を含む」行を選択するコード

SELECT *
  FROM Staging
 WHERE 1 = ANY(ARRAY[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10]);

SELECT *
  FROM Staging
 WHERE NOT 1 <> ALL(ARRAY[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10]);

パズル74

ネタ別解。可読性もパフォーマンスも悪い。最後の項目がこんなんでいいんですかね・・・？

多対多の結合の場合相手方の結合対象の数だけ重複行が発生するため、SUMするときに重複して計上してしまう。 なので、最終的に重複数で割ることで無理やり帳尻を合わせます。CASE WHEN してるのは０除算を防ぐため。 こうすれば中間テーブルは作られませんね（効率がいいとは言っていない）

SELECT A.acct_nbr,
       COALESCE(SUM(F.foo_qty), 0) / CASE WHEN EXISTS (SELECT * FROM Bar WHERE Bar.acct_nbr = A.acct_nbr) THEN (SELECT COUNT(*) FROM Bar WHERE Bar.acct_nbr = A.acct_nbr) ELSE 1 END AS foo_qty_tot,
       COALESCE(SUM(B.bar_qty), 0) / CASE WHEN EXISTS (SELECT * FROM Foo WHERE Foo.acct_nbr = A.acct_nbr) THEN (SELECT COUNT(*) FROM Foo WHERE Foo.acct_nbr = A.acct_nbr) ELSE 1 END AS bar_qty_tot
  FROM Accounts AS A
       LEFT OUTER JOIN 
       Foo AS F
       ON A.acct_nbr = F.acct_nbr
       LEFT OUTER JOIN
       Bar AS B
       ON A.acct_nbr = B.acct_nbr
 GROUP BY A.acct_nbr
 ORDER BY A.acct_nbr;