【kSQL 実践 #2】期間で集計する — 相対日付は押し下がり DATE_FORMAT は押し下がらない、無い月は GENERATE_SERIES で 0 にする

Posted at 2026-09-15

結論（3 行）

期間の絞り込みは WHERE に相対日付関数か日付リテラルで書く。THIS_YEAR() は kintone 側で評価され fetch: EXACT。同じ意図を DATE_FORMAT(列, '%Y') = '2026' と書くと全件取得になる

「商談の無い月」は GROUP BY では行ごと消える。GENERATE_SERIES の月系列に LEFT JOIN して 0 にする。0 埋めの前に LAG を取ると、空月を飛ばして2 か月前の行を参照する（実測）

ON に関数は書けない、LEFT JOIN の不一致は空文字、ウィンドウ関数の結果は同じ SELECT の式に使えない。段（CTE）を分けるのが kSQL の作法

前回（第 1 回: アプリをまたいで集計する）は JOIN の押し下げでした。今回は日付です。
「月別の推移と前月からの増減」は kintone の一覧グラフでもある程度作れますが、データの無い月が消える、前月との差が出せない、期間の切り方を変えるたびに一覧を作り直すの 3 つで詰まります。

課題

案件管理（APP4149）で、月別の初回商談件数の推移と**前月からの増減（前月差）**を出したい。商談が 1 件も無かった月も 0 として並べたい。
比率（前月比 %）ではなく差にしているのは、0 件の月が混ざると 0 除算の扱いが要り、この回の主題から外れるためです。

標準 SQL ならこう書く

PostgreSQL なら generate_series で月の軸を作り、date_trunc で結合します。

WITH months AS (
  SELECT generate_series('2025-11-01'::date, '2026-07-01'::date, '1 month') AS m
),
monthly AS (
  SELECT date_trunc('month', first_meeting_date) AS m, COUNT(*) AS deals
  FROM deals
  GROUP BY 1
)
SELECT months.m, COALESCE(monthly.deals, 0) AS deals,
       COALESCE(monthly.deals, 0) - LAG(COALESCE(monthly.deals, 0)) OVER (ORDER BY months.m) AS diff
FROM months LEFT JOIN monthly ON months.m = monthly.m
ORDER BY months.m

kSQL でも骨格は同じです。違うのは、ON に関数を書けない、LEFT JOIN の不一致が NULL ではなく空文字になる、LAG の結果を同じ SELECT の式に使えないの 3 点で、いずれも「段を 1 つ増やす」か「書き方を 1 つ変える」で解決します。

まず期間の絞り込み — 何が押し下がるか

月別に入る前に、期間条件の書き方で取得量が変わることを押さえます。「今年の案件」を 3 通りで書いて EXPLAIN します。

書き方	`fetch`	kintone query
`WHERE 初回商談日 = THIS_YEAR()`	EXACT	`初回商談日 = THIS_YEAR()`（kintone 側で評価）
`WHERE 初回商談日 >= '2026-01-01' AND 初回商談日 <= '2026-12-31'`	EXACT	範囲比較がそのまま載る
`WHERE DATE_FORMAT(初回商談日, '%Y') = '2026'`	ALL	`(全件取得)`。関数で包んだ列は変換できない

相対日付関数の EXPLAIN はこう出ます。

fetch summary: EXACT
  relative date function: THIS_YEAR
  relative date evaluation: kintone server whole-WHERE exact
  where capability: EXACT_PUSHDOWN
  server predicate: 初回商談日 = THIS_YEAR()
  client residual: (none)
  kintone query: 初回商談日 = THIS_YEAR()
  fetch:         EXACT

GROUP BY があっても WHERE 全体が kintone へ渡ります。日付条件に使えるのは、kintone クエリの TODAY() と、相対日付関数 12 個（YESTERDAY() / TOMORROW() / FROM_TODAY(n, unit) / THIS_WEEK() / LAST_WEEK() / NEXT_WEEK() / THIS_MONTH() / LAST_MONTH() / NEXT_MONTH() / THIS_YEAR() / LAST_YEAR() / NEXT_YEAR()）です。
相対日付関数 12 個は WHERE 専用で、SELECT 列や CASE の条件には書けません。TODAY() は WHERE と DECLARE で使えますが、これも SELECT 列には書けません（実測: ParseError）。SELECT 側で今日が要るときは CURRENT_DATE() を使います。こちらは押し下げ経路が無く、実行環境のローカル日付です。

規則は「WHERE に書く日付は相対日付関数かリテラル、SELECT で整形する日付は DATE_FORMAT」です。第 0 回の例 3 と同じ話で、今回はこれを月別集計の中で使います。

段階 1: GROUP BY だけでは月が消える

SELECT DATE_FORMAT(初回商談日, '%Y-%m') AS 年月, COUNT(*) AS 件数
FROM APP4149
WHERE 初回商談日 >= '2025-11-01' AND 初回商談日 < '2026-08-01'
GROUP BY DATE_FORMAT(初回商談日, '%Y-%m')
ORDER BY 年月

WHERE の範囲は前節のとおり fetch: EXACT で押し下がり、日付が空の案件もここで外れます（WHERE を外すと、空の日付は '' として 1 つのグループになります）。

年月	件数
2025-11	1
2026-01	2
2026-02	1
2026-03	1
2026-05	2
2026-06	5
2026-07	8

7 行です。2025-12 と 2026-04 がありません。GROUP BY はデータのある月しか行を作らないので、「0 件だった」と「そもそも無かった」の区別が付きません。
折れ線や棒にすると、X 軸から月が抜けて線が詰まります。

上の件数は手元環境の初期サンプルデータでの値です。SFA パックの日付は導入時期で変わるので、あなたの環境では抜ける月が違います。まずこの SQL を実行して、自分の環境でどの月が抜けるかを見てください。

ついでに落とし穴: 0 埋め前に `LAG` を取ると 2 か月前の行を参照する

前月差を出そうとして、この 7 行に LAG を掛けるとどうなるか。

WITH 月別 AS (
  SELECT DATE_FORMAT(初回商談日, '%Y-%m') AS 年月, COUNT(*) AS 件数
  FROM APP4149
  WHERE 初回商談日 >= '2025-11-01' AND 初回商談日 < '2026-08-01'
  GROUP BY DATE_FORMAT(初回商談日, '%Y-%m')
)
SELECT 年月, 件数, LAG(件数) OVER (ORDER BY 年月) AS 前行の件数
FROM 月別
ORDER BY 年月

年月	件数	前行の件数
2026-03	1	1
2026-05	2	1
2026-06	5	2

2026-05 の「前行」は 2026-03 です。4 月が無いので、LAG は直前に存在する行、つまり 2 か月前を返します。エラーも警告も出ません。
前月差は 0 埋めしてから取る。これがこの回の主題です。

段階 2: `GENERATE_SERIES` で月の軸を作り、LEFT JOIN で 0 を埋める

kintone に日付ディメンションのアプリはありませんが、WITH の CTE 本体に GENERATE_SERIES を書けば、レコードを 1 件も読まずに日付系列を作れます。

WITH 月系列 AS (
  GENERATE_SERIES('2025-11-01', '2026-07-01', '1 month') AS 月初
),
月キー AS (
  SELECT DATE_FORMAT(月初, '%Y-%m') AS 年月 FROM 月系列
),
月別 AS (
  SELECT DATE_FORMAT(初回商談日, '%Y-%m') AS 年月, COUNT(*) AS 件数
  FROM APP4149
  WHERE 初回商談日 >= '2025-11-01' AND 初回商談日 < '2026-08-01'
  GROUP BY DATE_FORMAT(初回商談日, '%Y-%m')
)
SELECT s.年月, CASE WHEN a.件数 = '' THEN 0 ELSE a.件数 END AS 件数
FROM 月キー AS s
LEFT JOIN 月別 AS a ON s.年月 = a.年月
ORDER BY s.年月

年月	件数
2025-11	1
2025-12	0
2026-01	2
2026-02	1
2026-03	1
2026-04	0
2026-05	2
2026-06	5
2026-07	8

9 行になり、空月が 0 で並びました。CTE が 3 段ある理由がそのまま kSQL の差分です。

月系列: GENERATE_SERIES(start, stop, step) は WITH の CTE 本体にしか書けません。FROM GENERATE_SERIES(...) の直書きは不可。月 step の start は月初（YYYY-MM-01）限定です。生成される値は DATE で、レコード API は呼びません（EXPLAIN では records API: none）
月キー: ON に関数は書けないので、ON DATE_FORMAT(s.月初, '%Y-%m') = a.年月 は ParseError になります。年月の文字列キーを CTE で列にしてから、列同士の等式で結びます
月別: 集計側。ここまでは段階 1 と同じ

最後の CASE WHEN a.件数 = '' THEN 0 は、LEFT JOIN で一致しなかった側が NULL ではなく空文字になるためです。
kSQL の COALESCE は「最初の空でない値」を返すので COALESCE(a.件数, 0) でも同じ結果になります（実測）。ここでは空文字が来ることを明示するために CASE で書いています。

GENERATE_SERIES は日次（'1 day'）や年次（'1 year'）、整数（GENERATE_SERIES(1, 100)）も作れます。同一 WITH 文内の生成行数の上限は 10,000 行（日次で約 27 年分）で、WHERE や LIMIT を付けても免除されません。

段階 3: 前月差 — ウィンドウ関数の結果は次の段で使う

0 埋めした 9 行に LAG を掛けて前月差を出します。ここでもう 1 つ差分に当たります。

-- NG: LAG の結果を同じ SELECT の式で使う
SELECT 年月, 件数, 件数 - LAG(件数) OVER (ORDER BY 年月) AS 前月差 FROM 埋め済み

ParseError: ウィンドウ関数の結果は同じ SELECT の式では使えません。
  × SELECT ROUND(SUM(x) OVER (), 1) AS a FROM t
  ○ WITH w AS (SELECT SUM(x) OVER () AS 総計 FROM t) SELECT ROUND(総計, 1) AS a FROM w

エラー文が答えを書いています。ウィンドウの結果を列として出し、それを使う式は次の段に書きます。

WITH 月系列 AS (
  GENERATE_SERIES('2025-11-01', '2026-07-01', '1 month') AS 月初
),
月キー AS (
  SELECT DATE_FORMAT(月初, '%Y-%m') AS 年月 FROM 月系列
),
月別 AS (
  SELECT DATE_FORMAT(初回商談日, '%Y-%m') AS 年月, COUNT(*) AS 件数
  FROM APP4149
  WHERE 初回商談日 >= '2025-11-01' AND 初回商談日 < '2026-08-01'
  GROUP BY DATE_FORMAT(初回商談日, '%Y-%m')
),
埋め済み AS (
  SELECT s.年月, CASE WHEN a.件数 = '' THEN 0 ELSE a.件数 END AS 件数
  FROM 月キー AS s LEFT JOIN 月別 AS a ON s.年月 = a.年月
),
前月付き AS (
  SELECT 年月, 件数, LAG(件数) OVER (ORDER BY 年月) AS 前月件数
  FROM 埋め済み
)
SELECT 年月, 件数, 前月件数, 件数 - 前月件数 AS 前月差
FROM 前月付き
ORDER BY 年月

年月	件数	前月件数	前月差
2025-11	1		1
2025-12	0	1	-1
2026-01	2	0	2
2026-02	1	2	-1
2026-03	1	1	0
2026-04	0	1	-1
2026-05	2	0	2
2026-06	5	2	3
2026-07	8	5	3

2026-05 の前月差が、段階 1 のように 3 月を前行として扱うのではなく、4 月（0 件）との差になりました。

2 つ注意があります。

先頭行の前月差が 1 になっている。 前月件数が無い（空文字）ので、算術では 0 として計算されます（第 0 回の「数値の空セルは算術で 0」）。先頭行は前月差を出さないなら CASE WHEN 前月件数 = '' THEN '' ELSE 件数 - 前月件数 END のように守ります
警告が出ます。「前月件数 の ORDER BY は全順序でないため…」。年月 は集約キーで一意なので、この警告は無視して構いません。警告文自身が「元の集約キーをすべて ORDER BY に含む形」は無視できると書いています

落とし穴 — 相対日付と CTE の組み合わせ

期間を「今年」に絞りつつ 0 埋めするなら、月別 の CTE に WHERE 初回商談日 = THIS_YEAR() を入れます。これは動きます。CTE の中でも、WHERE 全体が kintone へ渡せる形なら相対日付を使えます。

動かないのは、押し下げられない条件と AND で混ぜた形です。

-- NG: CTE の中で、相対日付と関数条件を AND で混ぜる
月別 AS (
  SELECT DATE_FORMAT(初回商談日, '%Y-%m') AS 年月, COUNT(*) AS 件数
  FROM APP4149
  WHERE 初回商談日 = THIS_YEAR() AND LENGTH(案件名) > 5
  GROUP BY DATE_FORMAT(初回商談日, '%Y-%m')
)

THIS_YEAR: WHERE_RELATIVE_DATE_REQUIRES_EXACT_PUSHDOWN (path=statement.cte[2])

トップレベルの単一アプリ SELECT なら、相対日付だけを kintone へ送り関数条件を残余として評価する PREFILTERED が使えますが、CTE 本体・一時テーブルの実体化では WHERE 全体が exact でなければなりません。

対処は 2 文に分けることです。相対日付だけの単独 SELECT で一時テーブルへ実体化し、関数条件は次の段で掛ける。

CREATE TEMP TABLE #対象 AS
SELECT 案件名, 初回商談日 FROM APP4149 WHERE 初回商談日 = THIS_YEAR();

WITH 月系列 AS (
  GENERATE_SERIES('2026-01-01', '2026-12-01', '1 month') AS 月初
),
月キー AS (
  SELECT DATE_FORMAT(月初, '%Y-%m') AS 年月 FROM 月系列
),
月別 AS (
  SELECT DATE_FORMAT(初回商談日, '%Y-%m') AS 年月, COUNT(*) AS 件数
  FROM #対象
  WHERE LENGTH(案件名) > 5
  GROUP BY DATE_FORMAT(初回商談日, '%Y-%m')
)
SELECT s.年月, CASE WHEN a.件数 = '' THEN 0 ELSE a.件数 END AS 件数
FROM 月キー AS s LEFT JOIN 月別 AS a ON s.年月 = a.年月
ORDER BY s.年月

1 文目は 初回商談日 = THIS_YEAR() が kintone 側で評価され、19 件だけ実体化されます。2 文目は一時テーブルに対する関数条件なので制約に掛かりません。第 1 回の「絞ってから実体化」と同じ形です。

落とし穴のまとめ

WHERE の日付は相対日付関数かリテラル。 DATE_FORMAT / YEAR() / MONTH() で包むと fetch: ALL
相対日付は WHERE 専用。 SELECT 列の期間判定は DATE_FORMAT や YEAR() で書く。CURRENT_DATE() は押し下げ経路が無く、実行環境のローカルタイムゾーンで評価される
CTE・一時テーブルで相対日付関数を使うなら、WHERE 全体が exact であること。 関数条件と AND で混ぜると WHERE_RELATIVE_DATE_REQUIRES_EXACT_PUSHDOWN。2 文に分ける。通常の関数条件だけなら、この制約は無い
ON に関数は書けない。 年月キーは CTE で列にしてから結ぶ
LEFT JOIN の不一致は空文字。 CASE WHEN x = '' THEN 0 か COALESCE(x, 0) で 0 にする（kSQL の COALESCE は空文字を非空値と扱わない）
ウィンドウ関数の結果は同じ SELECT の式で使えない。 次の段へ。集計と同じ SELECT にも書けない
0 埋め前の LAG は空月を飛ばす。 エラーも警告も出ない
先頭行の LAG は空文字で、算術では 0 扱い。 前月差を空にしたいなら CASE で守る
DATE_ADD(列, n, 'MONTH') の引数順は MySQL と違う。 DATE_SUB と INTERVAL は無い。月末丸めあり（2026-01-31 + 1 か月 = 2026-02-28）
月 step の start は月初限定。 月末が要るなら LAST_DAY(月初)。生成行数は同一 WITH で 10,000 まで

運用に載せる

無償版の kSQL Dashboard プラグインの棒グラフは「ラベル列 + 値列」なので、段階 2 の SQL がそのまま月別の棒になります。0 埋めしてあるので X 軸に月が抜けません。

期間を固定値（'2025-11-01'）で書くと、月が進むたびに直す必要があります。ダッシュボードに置くなら、系列の端を DECLARE で変数にしておくと保守が楽です。

DECLARE @from = '2026-01-01';
DECLARE @to = TODAY();
WITH 月系列 AS (
  GENERATE_SERIES(@from, @to, '1 month') AS 月初
),
月キー AS (
  SELECT DATE_FORMAT(月初, '%Y-%m') AS 年月 FROM 月系列
),
月別 AS (
  SELECT DATE_FORMAT(初回商談日, '%Y-%m') AS 年月, COUNT(*) AS 件数
  FROM APP4149
  WHERE 初回商談日 >= @from AND 初回商談日 <= @to
  GROUP BY DATE_FORMAT(初回商談日, '%Y-%m')
)
SELECT s.年月, CASE WHEN a.件数 = '' THEN 0 ELSE a.件数 END AS 件数
FROM 月キー AS s LEFT JOIN 月別 AS a ON s.年月 = a.年月
ORDER BY s.年月

stop（@to）は月初でなくてかまいません。月初限定なのは start だけです。同じ変数を系列の端と WHERE の範囲の両方に使うので、期間を変えても軸と集計がずれません。WHERE の変数は実行時に値へ解決されて押し下がります（EXPLAIN では pushdown candidate: 初回商談日 >= "@from" and 初回商談日 <= "@to"・fetch: EXACT）。
日付の既定値には TODAY() や CURRENT_DATE()、それらを使った DATE_ADD を書けます。一方、相対日付関数（THIS_MONTH() など）は WHERE 専用なので、通常の DECLARE には書けません。
なお DECLARE @to = TODAY() の TODAY() は kintone サーバーではなく、プラグインを実行している環境で既定値として 1 回評価され、WHERE には日付文字列として押し下がります。WHERE に直接書いた TODAY() が kintone 側で評価されるのとは経路が違います。プラグインでは DECLARE の既定値がそのまま使われ、CLI では --var で差し替えられます（第 7 回）。

Claude に頼むなら（MCP 導入済みの読者向け・任意）

kSQL MCP は第 9 回で導入します。導入済みなら、次の依頼文でこの回の内容を再現できます。

kSQL MCP で、APP4149（案件管理）の初回商談日を月別に数え、商談の無い月も 0 として並べる SQL を書いてください。
月の軸は GENERATE_SERIES で作り、LEFT JOIN の不一致は CASE で 0 にしてください。
そのうえで前月差の列を足してください。ウィンドウ関数の結果を同じ SELECT の式で使わない形にし、
ksql_validate で検証してください。実行はしないでください。

次回は第 3 回「ABC 分析」です。ウィンドウ関数で順位・構成比・累積構成比を出し、顧客を A/B/C に分けます。

リポジトリ・ドキュメント:

https://github.com/rex0220/kintone-sql-tools
npm: @rex0220/kintone-sql-tools（CLI / プラグイン / MCP サーバー同梱）

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up