SQLでカラムの合計値がある値を超えるまで出力するには #SQL

SQLで、テーブルをあるカラムの値でソートし、別のカラムの合計値が閾値を超えるまでのエントリを取り出す方法を書く。whileとかカーソルとかのループを使わずにやれないかと検索したらStack Overflowに一発で答えがあったけど、一応自分用のメモ書きで残しておく。

参考リンク

Stack Overflowのスレッド(SQL Server版): https://stackoverflow.com/questions/23479161/getting-sum-of-an-sql-table-column-until-the-sum-reaches-5000
MySQL版:
https://stackoverflow.com/questions/7097413/a-sql-query-to-select-until-sumusers-count-reaches-1000

テーブル例

create table Items
(
    ID int NOT NULL primary key clustered,
    Name nvarchar(255) not null,
    Price int not null,
    LastUpdated datetime2(7) not null
)

サンプルデータ

insert into Items (ID, Name, Price, LastUpdated) values
    (1, 'Tomato', 5, '2017-06-01'),
    (2, 'Banana', 4, '2017-05-30'),
    (3, 'Apple', 3, '2017-07-05'),
    (4, 'Potato', 3, '2017-04-01'),
    (5, 'Orange', 5, '2017-07-01'),
    (6, 'Broccoli', 4, '2017-07-04')

SQL構文

上記のデータからLastUpdated順に、Priceの合計が10を超えるまで表示するには以下のSQLクエリを使う。

WITH q AS (
    SELECT *, SUM(Price) OVER (ORDER BY LastUpdated) AS TotalPrice
    FROM Items
)
SELECT * FROM q
WHERE TotalPrice <= 10
ORDER BY LastUpdated

複数テーブルでもOK

上記のサンプルは正規化されてないテーブルだったが、より実用的に正規化されたテーブルを使って同じことをする。さらにWHERE句も追加し、ある条件に合致するエントリ(InStockが1のもの)だけを取り出す。

create table Items
(
    ID int NOT NULL primary key clustered,
    Name nvarchar(255) not null
)
GO

create table Prices
(
    ItemId int FOREIGN KEY REFERENCES Items(ID),
    Price int not null,
    InStock BIT not null,
    LastUpdated datetime2(7) not null
)
GO

サンプルデータ

insert into Items (ID, Name) values
    (1, 'Tomato'),
    (2, 'Banana'),
    (3, 'Apple'),
    (4, 'Potato'),
    (5, 'Orange'),
    (6, 'Broccoli')
GO

insert into Prices (ItemId, Price, InStock, LastUpdated) values
    (1, 5, 1, '2017-06-01'),
    (2, 4, 0, '2017-05-30'),
    (3, 3, 0, '2017-07-05'),
    (4, 3, 1, '2017-04-01'),
    (5, 5, 1, '2017-07-01'),
    (6, 4, 1, '2017-07-04')
GO

SQL構文

WITH q AS (
  SELECT SUM(P.Price) OVER (ORDER BY P.LastUpdated) AS TotalPrice
      ,I.*
      ,P.*
  FROM Items AS I
  JOIN Prices AS P ON I.ID = P.ItemId
  WHERE P.InStock = 1
)
SELECT * FROM q
WHERE TotalPrice <= 10
ORDER BY LastUpdated

IFとか変数とかカーソルとか一切不要。

おまけ

上記の例のExecution Planを見るとPriceテーブルに対してテーブルスキャンがかかっている。なのでインデックスを追加する。

create nonclustered index ix_price_lastupdated on Prices (LastUpdated)

これでテーブルスキャンがインデックススキャンに置き換わった。