スクロールバーが一番下にあるときのみ要素の追加時に自動スクロールする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;