スクロールバーが一番下にあるときのみ要素の追加時に自動スクロールするBehavior
前提条件:
・Xaml.Behaviors.Wpf がインストール済
・言語バージョン C#9.0
・Null安全のオプションがON
class AutoScrollBehavior : Behavior<ListBox> { //-----------------依存関係プロパティ-------------------------------- public static readonly DependencyProperty EndMarginProperty = DependencyProperty.Register( "EndMargin", typeof(double), typeof(AutoScrollBehavior), new FrameworkPropertyMetadata { DefaultValue = 0.0, BindsTwoWayByDefault = true }); public double EndMargin { get { return (double)GetValue(EndMarginProperty); } set { SetValue(EndMarginProperty, value); } } //------------------------------------------------------------------- ScrollViewer? _listBoxScroll; bool _shouldAutoScroll = true; protected override void OnAttached() { base.OnAttached(); this.AssociatedObject.Loaded += OnLoaded; } private void OnLoaded(object sender, RoutedEventArgs e) { _listBoxScroll = CaptureScrollViewer(); if (_listBoxScroll is not null) { _listBoxScroll.ScrollChanged += OnScrollChanged; } } private void OnScrollChanged(object sender, ScrollChangedEventArgs e) { if (e.VerticalChange != 0.0) { _shouldAutoScroll = e.ViewportHeight + e.VerticalOffset + EndMargin >= e.ExtentHeight; return; } if (_shouldAutoScroll) { _listBoxScroll = _listBoxScroll ?? CaptureScrollViewer(); _listBoxScroll?.ScrollToEnd(); } } protected override void OnDetaching() { base.OnDetaching(); if (_listBoxScroll is not null) { _listBoxScroll.ScrollChanged -= OnScrollChanged; } this.AssociatedObject.Loaded -= OnLoaded; } private ScrollViewer? CaptureScrollViewer() { var border = VisualTreeHelper.GetChild(AssociatedObject, 0) as Border; return border?.Child as ScrollViewer; } }
『SQLパズル 第2版』の別解など その4
『SQLパズル 第2版』の別解など その3 - 不思議の備忘録
の続き
パズル62
SQLメモ - 不思議の備忘録の「二人組作って~」のN列に一般化 ――3列以上に拡張したい場合も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関数を使った答え2の別解(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
答え2や3をみるに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);
この場合答えその2とその3は違うクエリ結果になります(列持ち行もちの違いではなく)
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'は2つあるので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 してるのは0除算を防ぐため。 こうすれば中間テーブルは作られませんね(効率がいいとは言っていない)
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;
『SQLパズル 第2版』の別解など その3
『SQLパズル 第2版』の別解など その2 - 不思議の備忘録
の続き
パズル45
■別解 GROUP BY句にCASE式
SELECT句の特性関数はCASE式を用いたGROUPBY句と相互に変換できます
SELECT cust_id ,CASE WHEN bill_date BETWEEN CURRENT_DATE - INTERVAL '30' DAY AND CURRENT_DATE THEN '0-30days_ago' WHEN bill_date BETWEEN CURRENT_DATE - INTERVAL '60' DAY AND CURRENT_DATE - INTERVAL '31' DAY THEN '31-60days_ago' WHEN bill_date BETWEEN CURRENT_DATE - INTERVAL '90' DAY AND CURRENT_DATE - INTERVAL '61' DAY THEN '61-90days_ago' ELSE '91-days_ago' END AS span ,SUM(pizza_amt) AS total_pizza_amt FROM FriendsofPepperoni GROUP BY cust_id, span ORDER BY cust_id, span;
パズル46
答え1について、スカラサブクエリをP1.start_dateにするとなぜ動かないのか。それは、集約キーじゃないので集合として存在してるから。 ゆえに、GROUP BY 句に列を追加するか、下のようにMAXでとりだす必要があります。
SELECT S1.clerk_name, P1.promo_name, SUM(S1.sale_amt) AS sales_tot FROM Sales AS S1, Promotions AS P1 WHERE S1.sale_date BETWEEN P1.start_date AND P1.end_date GROUP BY S1.clerk_name, P1.promo_name HAVING SUM(sale_amt) >= ALL ( SELECT SUM(sale_amt) FROM Sales AS S2 WHERE S2.clerk_name <> S1.clerk_name AND S2.sale_date BETWEEN MAX(P1.start_date) AND MAX(P1.end_date) GROUP BY S2.clerk_name);
パズル52
集約関数の中に集約関数は入れられないが、Window関数の中に集約関数をいれることができる。(類:パズル33の別解) これで疑似的に入れ子の集約関数(2レベルまで)を実現できる
SELECT DISTINCT AVG(COUNT(*)) OVER() FROM Personnel GROUP BY dept_id; INSERT INTO Personnel VALUES('A');
パズル53
■Window関数を用いた別解
SELECT MIN(CASE WHEN cc = 1 THEN color ELSE NULL END) AS color ,MIN(CASE WHEN cl = 1 THEN length ELSE NULL END) AS length ,MIN(CASE WHEN cw = 1 THEN width ELSE NULL END) AS width ,MIN(CASE WHEN ch = 1 THEN hgt ELSE NULL END) AS hgt FROM (SELECT lvl ,color ,length ,width ,hgt ,COUNT(color) OVER(ORDER BY lvl DESC) cc ,COUNT(length) OVER(ORDER BY lvl DESC) AS cl ,COUNT(width) OVER(ORDER BY lvl DESC) AS cw ,COUNT(hgt) OVER(ORDER BY lvl DESC) AS ch FROM Foobar) AS TMP;
パズル55
RacingResultsテーブルしかない場合について、UNIONをCASE式で置き換える方法
CREATE TABLE OneTwoThree(num INTEGER); INSERT INTO OneTwoThree VALUES(1); INSERT INTO OneTwoThree VALUES(2); INSERT INTO OneTwoThree VALUES(3); SELECT horse_name, COUNT(*) FROM (SELECT track_id ,race_date ,race_nbr ,OTT.num AS rank ,CASE OTT.num WHEN 1 THEN win_name WHEN 2 THEN place_name WHEN 3 THEN show_name END AS horse_name FROM RacingResults CROSS JOIN OneTwoThree AS OTT) AS TMP GROUP BY horse_name;
順位ごとにも
SELECT horse_name, rank, COUNT(*) FROM (SELECT track_id ,race_date ,race_nbr ,OTT.num AS rank ,CASE OTT.num WHEN 1 THEN win_name WHEN 2 THEN place_name WHEN 3 THEN show_name END AS horse_name FROM RacingResults CROSS JOIN OneTwoThree AS OTT) AS TMP GROUP BY horse_name, rank;
HorseNamesがあるとき何着に何回なったかのクエリ(これが一番シンプルですかね)
SELECT H1.horse ,CASE WHEN H1.horse = win_name THEN 1 WHEN H1.horse = place_name THEN 2 WHEN H1.horse = show_name THEN 3 ELSE NULL END AS rank ,COUNT(*) FROM HorseNames AS H1, RacingResults AS R1 WHERE H1.horse IN (R1.win_name, R1.place_name, R1.show_name) GROUP BY H1.horse, rank; --単純CASE式のバージョン SELECT H1.horse ,CASE H1.horse WHEN win_name THEN 1 WHEN place_name THEN 2 WHEN show_name THEN 3 ELSE NULL END AS rank ,COUNT(*) FROM HorseNames AS H1, RacingResults AS R1 WHERE H1.horse IN (R1.win_name, R1.place_name, R1.show_name) GROUP BY H1.horse, rank; SELECT H1.horse ,CASE H1.horse WHEN win_name THEN 1 WHEN place_name THEN 2 WHEN show_name THEN 3 ELSE NULL END AS rank ,COUNT(win_name) FROM HorseNames AS H1 LEFT OUTER JOIN RacingResults AS R1 ON H1.horse IN (R1.win_name, R1.place_name, R1.show_name) GROUP BY H1.horse, rank;
パズル59
問題設定としてはパズル3と似ていますが、5日と6日の間が切れるっていうのが最大の違いであり、そこが難しくしている要因ですね。
パターンの網羅のためにもこれも入れておきます
INSERT INTO Timesheets VALUES (11, '1997-01-13', '1997-01-13');
■1日を3つに分割する別解
この別解は非常に複雑で図を用いた詳細な解説が必要だと思います。(このクエリから意図を読むのは難しいのではないでしょうか?) 解説が完成したら別記事で解説します。
CREATE TABLE Days199701(date DATE NOT NULL); INSERT INTO Days199701 VALUES ('1997-01-01'); INSERT INTO Days199701 VALUES ('1997-01-02'); INSERT INTO Days199701 VALUES ('1997-01-03'); INSERT INTO Days199701 VALUES ('1997-01-04'); INSERT INTO Days199701 VALUES ('1997-01-05'); INSERT INTO Days199701 VALUES ('1997-01-06'); INSERT INTO Days199701 VALUES ('1997-01-07'); INSERT INTO Days199701 VALUES ('1997-01-08'); INSERT INTO Days199701 VALUES ('1997-01-09'); INSERT INTO Days199701 VALUES ('1997-01-10'); INSERT INTO Days199701 VALUES ('1997-01-11'); INSERT INTO Days199701 VALUES ('1997-01-12'); INSERT INTO Days199701 VALUES ('1997-01-13'); INSERT INTO Days199701 VALUES ('1997-01-14'); INSERT INTO Days199701 VALUES ('1997-01-15'); INSERT INTO Days199701 VALUES ('1997-01-16'); INSERT INTO Days199701 VALUES ('1997-01-17'); INSERT INTO Days199701 VALUES ('1997-01-18'); INSERT INTO Days199701 VALUES ('1997-01-19'); INSERT INTO Days199701 VALUES ('1997-01-20'); INSERT INTO Days199701 VALUES ('1997-01-21'); INSERT INTO Days199701 VALUES ('1997-01-22'); INSERT INTO Days199701 VALUES ('1997-01-23'); INSERT INTO Days199701 VALUES ('1997-01-24'); INSERT INTO Days199701 VALUES ('1997-01-25'); INSERT INTO Days199701 VALUES ('1997-01-26'); INSERT INTO Days199701 VALUES ('1997-01-27'); INSERT INTO Days199701 VALUES ('1997-01-28'); INSERT INTO Days199701 VALUES ('1997-01-29'); INSERT INTO Days199701 VALUES ('1997-01-30'); INSERT INTO Days199701 VALUES ('1997-01-31');
SELECT MIN(date) AS start_date ,MAX(date) AS end_date FROM (SELECT date ,CEIL(SUM(COUNT(CASE WHEN cnt = 0 THEN 1 ELSE NULL END)) OVER(ORDER BY date) / 2 ) AS pair_num FROM (SELECT date ,(SELECT COUNT(*) FROM Timesheets WHERE date BETWEEN start_date + 1 AND end_date) AS cnt FROM Days199701 UNION ALL SELECT date ,(SELECT COUNT(*) FROM Timesheets WHERE date BETWEEN start_date AND end_date) AS cnt FROM Days199701 UNION ALL SELECT date ,(SELECT COUNT(*) FROM Timesheets WHERE date BETWEEN start_date AND end_date - 1) AS cnt FROM Days199701) AS TMP GROUP BY date HAVING MIN(cnt) = 0 AND MAX(cnt) > 0 ORDER BY date) AS TMP2 GROUP BY pair_num;
■答え1の補足
AND T3.end_date <= T2.end_date --T3.end_date < T4.start_dateがあるから不要 AND T4.start_date >= T1.start_date --T3.end_date < T4.start_dateがあるから不要
■答え2の補足 答え2は以下のクエリと同じ結果を得ることができます。
SELECT start_date, end_date FROM (SELECT T1.start_date ,ROW_NUMBER() OVER(ORDER BY T1.start_date) FROM Timesheets AS T1 LEFT OUTER JOIN Timesheets AS T2 ON T1.start_date > T2.start_date AND T1.start_date <= T2.end_date GROUP BY T1.start_date HAVING COUNT(T2.start_date) = 0 ) AS X(start_date, rn) INNER JOIN (SELECT T3.end_date ,ROW_NUMBER() OVER(ORDER BY T3.end_date) FROM Timesheets AS T3 LEFT OUTER JOIN Timesheets AS T4 ON T3.end_date < T4.end_date AND T3.end_date >= T4.start_date GROUP BY T3.end_date HAVING COUNT(T4.start_date) = 0 ) AS Y(end_date, rn) ON X.rn = Y.rn;
結局何がしたいのかというと2つの行数の同じテーブルを横にならべて合体させることですね。
また、答え2の
SELECT T1.start_date FROM Timesheets AS T1 LEFT OUTER JOIN Timesheets AS T2 ON T1.start_date > T2.start_date AND T1.start_date <= T2.end_date GROUP BY T1.start_date HAVING COUNT(T2.start_date) = 0
この部分は以下と同じ
SELECT T1.start_date FROM Timesheets AS T1 LEFT OUTER JOIN Timesheets AS T2 ON T1.start_date > T2.start_date AND T1.start_date <= T2.end_date WHERE T2.start_date IS NULL
さらに、これとも同じ
SELECT T1.start_date FROM Timesheets AS T1 WHERE NOT EXISTS (SELECT * FROM Timesheets AS T2 WHERE T1.start_date > T2.start_date AND T1.start_date <= T2.end_date)
EXISTS(NOT EXISTS)述語は1行見つかった時点で判定を打ち切るため、これが一番速いのではないでしょうか
■別視点での別解
この問題を解いていて思ったのが「歯抜け」から探したほうが手っ取り早いのではないかということです。具体的には
SELECT DISTINCT T1.end_date, T2.start_date FROM Timesheets AS T1 INNER JOIN Timesheets AS T2 ON T1.end_date < T2.start_date AND NOT EXISTS (SELECT * FROM Timesheets AS T3 WHERE T1.end_date + time '00:01' BETWEEN T3.start_date AND T3.end_date OR T2.start_date - time '00:01' BETWEEN T3.start_date AND T3.end_date OR T3.start_date BETWEEN T1.end_date + time '00:01' AND T2.start_date - time '00:01' OR T3.end_date BETWEEN T1.end_date + time '00:01' AND T2.start_date - time '00:01' );
または
SELECT DISTINCT T1.end_date, T2.start_date FROM Timesheets AS T1 INNER JOIN Timesheets AS T2 ON T1.end_date < T2.start_date AND NOT EXISTS (SELECT * FROM Timesheets AS T3 WHERE NOT( T1.end_date + time '00:01' >= T3.end_date OR T2.start_date - time '00:01' <= T3.start_date ) );
ほかには
WITH TMP AS (SELECT DISTINCT T1.end_date AS gap_start_date ,T2.start_date AS gap_end_date FROM Timesheets AS T1 INNER JOIN Timesheets AS T2 ON T1.end_date < T2.start_date AND NOT EXISTS (SELECT * FROM Timesheets AS T3 WHERE NOT( T1.end_date + time '00:01' >= T3.end_date OR T2.start_date - time '00:01' <= T3.start_date ) ) ) SELECT (SELECT MIN(start_date) FROM Timesheets) AS start_date, (SELECT MIN(gap_start_date) FROM TMP) AS end_date UNION ALL SELECT gap_end_date AS start_date ,COALESCE(MAX(gap_start_date) OVER(ORDER BY gap_end_date ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), (SELECT MAX(end_date) FROM Timesheets)) FROM TMP;
『SQLパズル 第2版』の別解など その2
『SQLパズル 第2版』の別解など その1 - 不思議の備忘録
の続き
パズル27
■答え2を正しく動作するように改良
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);
答え5の"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
答え3について、結合でT2のほうを使っていて個人的には以下のようにスカラサブクエリを使ったほうが直感的にわかりやすい気がする
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;
『SQLパズル 第2版』の別解など その1
SQLパズルの主にWindow関数を用いた別解やその他備忘録
実行環境はPostgreSQL13
パズル2
■Window関数を用いた別解
DELETE FROM Personnel WHERE emp_id IN (SELECT emp_id FROM (SELECT emp_id ,absent_date ,severity_points ,MIN(absent_date) OVER (PARTITION BY emp_id ORDER BY absent_date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_date FROM Absenteeism) AS A2 WHERE (absent_date <> pre_date + interval '1 day' OR pre_date IS NULL) AND absent_date BETWEEN current_date - interval '1 year' AND current_date GROUP BY emp_id HAVING SUM(severity_points) >= 6);
パズル3
■Window関数を用いた別解
SELECT proc_id ,anest_name ,start_time ,end_time ,COUNT(CASE start_time) OVER (PARTITION BY anest_name) FROM Procs;
パズル9
■連続する番号をグループ化
SELECT MIN(seat) ,'~' AS ~ ,MAX(seat) FROM (SELECT seat ,seat - ROW_NUMBER() OVER(ORDER BY seat) AS cgroup FROM Restaurant) AS GR GROUP BY cgroup ORDER BY cgroup;
■連続した欠番をグループ化
SELECT seat - (cgroup - precgroup) AS from ,'~' AS ~ ,seat - 1 AS to FROM (SELECT seat ,cgroup ,MAX(cgroup) OVER(ORDER BY seat ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS precgroup FROM (SELECT seat ,seat - ROW_NUMBER() OVER(ORDER BY seat) AS cgroup FROM Restaurant) AS GR1) AS GR2 WHERE cgroup > precgroup;
または欠番の補集合に連続した番号のグループ化を適用してもいい
パズル10
■Window関数を用いた別解
month_cnt = 0 の年のときはインクリメントしない行番号を生成し、これを通常の行番号から引き算することで month_cnt = 0 に挟まれた行で同じ値になる(GROUPにできる)要素を引き出せる
SELECT sin ,MAX(pen_year) AS end_year ,SUM(month_cnt) AS total_cnt ,SUM(earnings) AS total_earn FROM (SELECT sin ,pen_year ,month_cnt ,earnings ,ROW_NUMBER() OVER(PARTITION BY sin ORDER BY pen_year DESC) - accum_year_cnt AS cgroup FROM (SELECT sin ,pen_year ,month_cnt ,earnings ,COUNT(CASE WHEN month_cnt > 0 THEN month_cnt ELSE NULL END) OVER(PARTITION BY sin ORDER BY pen_year DESC) AS accum_year_cnt FROM Pensions) AS P1) AS P2 WHERE month_cnt > 0 GROUP BY sin, cgroup HAVING SUM(month_cnt) >= 60 ORDER BY sin, end_year;
集計関数を入れ子にできるなら(たしかOracleは1段だけ入れ子にできた)このようなSQLもいけるはず
SELECT sin ,MAX(CASE WHEN accum_cnt_traceback - MIN(accum_cnt_traceback) >= 60 THEN pen_year ELSE NULL END) AS start_year ,MAX(pen_year) AS end_year ,SUM(month_cnt) AS total_cnt ,SUM(earnings) AS total_earn FROM (SELECT sin ,pen_year ,month_cnt ,earnings ,accum_cnt_traceback ,ROW_NUMBER() OVER(PARTITION BY sin ORDER BY pen_year DESC) - accum_year_cnt AS cgroup FROM (SELECT sin ,pen_year ,month_cnt ,earnings ,SUM(month_cnt) OVER(PARTITION BY sin ORDER BY pen_year DESC) AS accum_cnt_traceback ,COUNT(CASE WHEN month_cnt > 0 THEN month_cnt ELSE NULL END) OVER(PARTITION BY sin ORDER BY pen_year DESC) AS accum_year_cnt FROM Pensions) AS P1) AS P2 WHERE month_cnt > 0 GROUP BY sin, cgroup HAVING SUM(month_cnt) >= 60 ORDER BY sin, end_year;
最後の別解は複雑なのでまずSQLについて連続を扱う方法について、思いついた方法について説明します。 今回のように month_cnt = 0 があるとリセットされるような「連続であることが前提として求められる」ようなものはSQLは苦手としています。 そこでつぎのテーブルを見てみましょう。
pen_year | month_cnt ----------+----------- 2001 | 1 2002 | 2 2003 | 3 2004 | 4 2005 | 0 2006 | 3 2007 | 3 2008 | 3 2009 | 0 2010 | 2 2011 | 0
このテーブルにたいして以下のCase式とWindow関数を組み合わせたもの
CASE WHEN month_cnt = 0 THEN -SUM(month_cnt) OVER(PARTITION BY sin ORDER BY pen_year) ELSE month_cnt END accum_cnt
を使って列を生成すると以下のテーブルになります。
pen_year | month_cnt | accum_cnt ----------+-----------+----------- 2001 | 1 | 1 2002 | 2 | 2 2003 | 3 | 3 2004 | 4 | 4 2005 | 0 | -10 2006 | 3 | 3 2007 | 3 | 3 2008 | 3 | 3 2009 | 0 | -9 2010 | 2 | 2 2011 | 0 | -2
さらにこのテーブルにたいして以下のWindow関数を使って列を生成すると
SUM(accum_cnt) OVER(PARTITION BY sin ORDER BY pen_year) AS part_sum
こうなります
pen_year | month_cnt | accum_cnt | part_sum ----------+-----------+-----------+---------- 2001 | 1 | 1 | 1 2002 | 2 | 2 | 3 2003 | 3 | 3 | 6 2004 | 4 | 4 | 10 2005 | 0 | -10 | 0 <-- month_cnt = 0 のときに累計値がリセットされる 2006 | 3 | 3 | 3 2007 | 3 | 3 | 6 2008 | 3 | 3 | 9 2009 | 0 | -9 | 0 <-- month_cnt = 0 のときに累計値がリセットされる 2010 | 2 | 2 | 2 2011 | 0 | -2 | 0 <-- month_cnt = 0 のときに累計値がリセットされる
まるで month_cnt = 0 の行でパーティションを切ってSUM(month_cnt) しているように見えるでしょう。 これをつかって年金の「連続」の方の条件を満たしていることを先に保証しちゃいます。
条件を満たす期間で最も直近の期間がほしいため逆方向からのカウントも用意する必要があります。 条件を満たす最も直近の期間を求めるビューは以下のようになって
CREATE VIEW MOST_RECENT_VALID_PENSION_RANGE AS SELECT sin ,MAX(CASE WHEN accum_cnt_trace_desc_in_group >= 60 THEN pen_year ELSE NULL END) AS start_year ,MAX(CASE WHEN accum_cnt_trace_asc_in_group >= 60 THEN pen_year ELSE NULL END) AS end_year FROM (SELECT sin ,pen_year ,month_cnt ,SUM(accum_cnt_trace_desc) OVER(PARTITION BY sin ORDER BY pen_year DESC) AS accum_cnt_trace_desc_in_group ,SUM(accum_cnt_trace_asc) OVER(PARTITION BY sin ORDER BY pen_year) AS accum_cnt_trace_asc_in_group FROM (SELECT sin ,pen_year ,month_cnt ,earnings ,CASE WHEN month_cnt = 0 THEN -SUM(month_cnt) OVER(PARTITION BY sin ORDER BY pen_year DESC) ELSE month_cnt END accum_cnt_trace_desc ,CASE WHEN month_cnt = 0 THEN -SUM(month_cnt) OVER(PARTITION BY sin ORDER BY pen_year) ELSE month_cnt END accum_cnt_trace_asc FROM Pensions) AS P1) AS P2 WHERE month_cnt > 0 GROUP BY sin;
その期間に対してearningの合計をサブクエリで出します。
SELECT sin ,(SELECT SUM(month_cnt) FROM Pensions WHERE M.sin = Pensions.sin AND pen_year BETWEEN start_year AND end_year) ,(SELECT SUM(earnings) FROM Pensions WHERE M.sin = Pensions.sin AND pen_year BETWEEN start_year AND end_year) FROM MOST_RECENT_VALID_PENSION_RANGE AS M ORDER BY sin;
SQLメモ
テストもかねて「SQLパズル」を解いていたときのメモを残しておきます。
実行環境はPostgreSQL13
二人組を作る
CREATE TABLE Nums(num INTEGER NOT NULL); INSERT INTO Nums VALUES(2); INSERT INTO Nums VALUES(3); INSERT INTO Nums VALUES(5); INSERT INTO Nums VALUES(5); INSERT INTO Nums VALUES(8); INSERT INTO Nums VALUES(9);
上のようなテーブルに対して2行ずつをペアにして表示したい
- 自己結合+集約
SELECT N1.num AS num1, MIN(N2.num) AS num2 FROM (SELECT num ,ROW_NUMBER() OVER(ORDER BY num) AS rn FROM Nums) AS N1 INNER JOIN (SELECT num ,ROW_NUMBER() OVER(ORDER BY num) AS rn FROM Nums) AS N2 ON N1.rn % 2 = 1 AND N1.rn < N2.rn GROUP BY N1.num;
- Window関数でグループ番号を振る
SELECT MIN(num) AS num1, MAX(num) AS num2 FROM (SELECT num ,(ROW_NUMBER() OVER(ORDER BY nums) + 1) / 2 AS pair_num FROM Nums) AS TMP GROUP BY pair_num ORDER BY num1;
3列以上に拡張したい場合はこうする
SELECT MAX(CASE col WHEN 1 THEN num END) AS num1 ,MAX(CASE col WHEN 2 THEN num END) AS num2 ,MAX(CASE col WHEN 3 THEN num END) AS num3 FROM (SELECT num ,(ROW_NUMBER() OVER(ORDER BY nums) - 1) / 3 + 1 AS row ,(ROW_NUMBER() OVER(ORDER BY nums) - 1) % 3 + 1 AS col FROM Nums) AS TMP GROUP BY row ORDER BY num1;
- Window関数で次の行を見る
SELECT num AS num1, next_num AS num2 FROM (SELECT num ,MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_num ,ROW_NUMBER() OVER(ORDER BY nums) AS rn FROM Nums) AS TMP WHERE rn % 2 = 1;
二人組を作る(ぼっちもいるパターン)
CREATE TABLE SPAN(num INTEGER UNIQUE NOT NULL, start_or_end CHAR(2) NOT NULL); INSERT INTO SPAN VALUES(2, 's'); INSERT INTO SPAN VALUES(3, 'e'); INSERT INTO SPAN VALUES(6, 's'); INSERT INTO SPAN VALUES(9, 'e'); INSERT INTO SPAN VALUES(11, 'se'); INSERT INTO SPAN VALUES(15, 's'); INSERT INTO SPAN VALUES(19, 'e'); INSERT INTO SPAN VALUES(20, 'se'); INSERT INTO SPAN VALUES(21, 'se'); INSERT INTO SPAN VALUES(22, 's'); INSERT INTO SPAN VALUES(25, 'e');
num | start_or_end ------------------ 2 | 's' 3 | 'e' 6 | 's' 9 | 'e' 11 | 'se' 15 | 's' 19 | 'e' 20 | 'se' 21 | 'se' 22 | 's' 25 | 'e'
こんなテーブルがあったとして このテーブルから下の表をだしたい
start | end ------+----- 2 | 3 6 | 9 11 | 11 15 | 19 20 | 20 21 | 21 22 | 25
始めかつ終わりなやつが1行であらわされているのがやりづらい numが重複を許さないため(11,'s')(11,'e')みたいに2行もちができない
- 自己結合+集約
SELECT S1.num AS start, MIN(S2.num) AS end FROM Span AS S1 INNER JOIN Span AS S2 ON CASE S1.start_or_end WHEN 'se' THEN (S1.num = S2.num ) WHEN 's' THEN (S2.start_or_end = 'e' AND S1.num < S2.num ) ELSE false END GROUP BY S1.num ORDER BY S1.num;
- Window関数を使う
SELECT MIN(num) AS start, MAX(num) AS end FROM (SELECT num ,CEIL((SUM((CASE WHEN start_or_end ='se' THEN 2 ELSE 1 END)) OVER(ORDER BY num) + 1) / 2 ) AS pair_num FROM Span) AS TMP GROUP BY pair_num ORDER BY MIN(num);
最大と最小を除いた平均
SELECT AVG(CASE acct_balance WHEN max THEN NULL WHEN min THEN NULL ELSE acct_balance END) FROM (SELECT customer_id ,acct_balance ,MIN(acct_balance) OVER() AS min ,MAX(acct_balance) OVER() AS max FROM Customers) AS TMP;