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;

３列以上に拡張したい場合はこうする

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;