Grafana パネル SQL¶

接続とデータソース追加は Grafana 接続を先に行う。

手順 5: ダッシュボード（予算残りが主目的）¶

方針の詳細は月次予算。 Discord と同じ定義:

変動枠 M = ceiling_yen − savings_yen − fixed_costs_yen
         （= budget_months.variable_pool_yen）

固定費はマスタ定額。変動カテゴリ枠は budget_month_categories のみ。未割当と月末実残は別枠貯金（surplus_ledger）。

Dashboard → New → パネル追加 → データソース PostgreSQL → Query は Code。 Stat / Table / Gauge は Format Table。

前提:

007_budget_months.sql + 011_fixed_and_surplus.sql 適用済み（sudo systemctl restart finance-manager）
当月の budget_months 行がある（Bot 起動または Discord で自動作成）
固定費マスタを入れるなら sql/budget_seed.example.sql を参考に seed
finance_readonly に SELECT 権限

月次 / 週次 / 日次で見たいもの（定義）¶

変動枠 M = variable_pool_yen を期間に割る（または各変動カテゴリ枠を按分）。

period	期間の変動予算	期間の使用	見たい指標
`day`	`M ÷ その月の日数`	今日の変動支出	1日分の枠に対して今日どれだけ使ったか
`week`	`M × 7 ÷ 日数`	今週（月曜〜）の変動支出	1週分の枠に対して今週どれだけ使ったか
`month`	`M`	今月の変動支出	変動枠に対してどれだけ使ったか

大分類も同じ按分（例: 食費月6万 → 日次は 60000÷日数）。固定費マスタに紐づく expense_category の支出は変動消化から外す。

右上の時間ピッカー（Last 1 year 等）は使わない。変数 period だけを見る。

変数の作り方¶

ダッシュボード設定 → Variables → Add
Name: period
Type: Custom
Values は 次のどちらか（混在注意）:

おすすめ（シンプル）:

day, week, month

日本語ラベル付きにする場合は SQL 側で 日次/週次/月次 も分岐する（下のクエリ済み）。 Grafana によっては ${period} に ラベル（日次） が入ることがあり、WHEN 'day' だけだと常に月次になる。

値が変わらないとき（よくある原因）¶

サマリ表で「期間」列が 日次/週次 なのに「この期間の予算」が月次と同じ 230000 のままになる。 変数の値が日本語ラベル（日次）なのに、SQL が WHEN 'day' しか見ていない。

その結果 CASE が全部 ELSE（月次）になり、予算按分も支出期間も月のままになる。

対処（どちらか）:

変数 Values を day, week, month だけにする（ラベル無し）して SQL を貼り直す
または下の SQL（day と 日次 両対応）に パネルごと差し替え

確認: 日次にしたとき「この期間の予算」≈ 230000÷その月の日数（例: 約7400）。週次なら ≈ 230000×7÷日数。

パネル A: 期間サマリ（Table、まずこれを置く）¶

period を変えると数値がはっきり変わるはずの本命。

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1::numeric
      WHEN '日次' THEN 1::numeric
      WHEN 'week' THEN 7::numeric
      WHEN '週次' THEN 7::numeric
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::numeric
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::numeric AS month_days
),
bm AS (
  SELECT m.variable_pool_yen::numeric AS month_spendable
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
fixed_cats AS (
  SELECT DISTINCT expense_category AS category
  FROM fixed_cost_items
  WHERE active AND expense_category IS NOT NULL AND expense_category <> ''
),
spent AS (
  SELECT COALESCE(SUM(e.amount_yen), 0)::numeric AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
    AND COALESCE(e.category, 'その他') NOT IN (SELECT category FROM fixed_cats)
)
SELECT
  ${period:singlequote} AS "期間",
  ROUND((SELECT month_spendable FROM bm), 0) AS "月次の変動枠",
  ROUND(
    (SELECT month_spendable FROM bm)
      * (SELECT period_days FROM bounds)
      / (SELECT month_days FROM bounds),
    0
  ) AS "この期間の予算",
  ROUND((SELECT spent_yen FROM spent), 0) AS "この期間の使用",
  ROUND(
    (SELECT month_spendable FROM bm)
      * (SELECT period_days FROM bounds)
      / (SELECT month_days FROM bounds)
    - (SELECT spent_yen FROM spent),
    0
  ) AS "この期間の残り",
  ROUND(
    100.0 * (SELECT spent_yen FROM spent)
      / NULLIF(
        (SELECT month_spendable FROM bm)
          * (SELECT period_days FROM bounds)
          / (SELECT month_days FROM bounds),
        0
      ),
    1
  ) AS "消化率%"
;

Visualization: Table / Format: Table

例（イメージ）: 変動枠が 130000 のとき

月次: 期間予算 = 130000、使用 = 今月の変動支出
日次: 期間予算 = 130000÷31 ≈ 4194、使用 = 今日だけ
週次: 期間予算 = 130000×7÷31 ≈ 29355、使用 = 今週だけ

パネル 0: 給与→固定→変動の内訳（Table）¶

SELECT
  m.ceiling_yen AS "前月給与",
  m.savings_yen AS "固定貯金",
  m.fixed_costs_yen AS "固定費",
  m.variable_pool_yen AS "変動枠",
  m.unallocated_to_surplus_yen AS "未割当→別枠(確定時)",
  m.status AS "状態"
FROM budget_months m
WHERE m.year_month = date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date;

パネル 1: 月次の変動枠（Stat、参考）¶

SELECT variable_pool_yen AS month_variable_pool_yen
FROM budget_months
WHERE year_month = date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date;

パネル 2: この期間の予算（Stat）¶

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1::numeric
      WHEN '日次' THEN 1::numeric
      WHEN 'week' THEN 7::numeric
      WHEN '週次' THEN 7::numeric
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::numeric
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::numeric AS month_days
),
bm AS (
  SELECT m.variable_pool_yen::numeric AS month_spendable
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
)
SELECT ROUND(
  (SELECT month_spendable FROM bm)
    * (SELECT period_days FROM bounds)
    / (SELECT month_days FROM bounds),
  0
) AS period_budget_yen;

パネル 3: この期間の使用（Stat）¶

WITH bounds AS (
  SELECT
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end
)
SELECT COALESCE(SUM(e.amount_yen), 0) AS period_spent_yen
FROM expenses e, bounds b
WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
  AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end;

パネル 3b: この期間の消化率%（Stat / Gauge）¶

全体予算の期間按分に対する使用率。 Gauge なら Min=0 / Max=100。

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1::numeric
      WHEN '日次' THEN 1::numeric
      WHEN 'week' THEN 7::numeric
      WHEN '週次' THEN 7::numeric
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::numeric
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::numeric AS month_days
),
bm AS (
  SELECT m.variable_pool_yen::numeric AS month_spendable
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
spent AS (
  SELECT COALESCE(SUM(e.amount_yen), 0)::numeric AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
)
SELECT ROUND(
  100.0 * (SELECT spent_yen FROM spent)
    / NULLIF(
      (SELECT month_spendable FROM bm)
        * (SELECT period_days FROM bounds)
        / (SELECT month_days FROM bounds),
      0
    ),
  1
) AS period_usage_pct;

パネル 4: 大分類ごと「予算 / 使った / 残り」（Table）¶

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1
      WHEN '日次' THEN 1
      WHEN 'week' THEN 7
      WHEN '週次' THEN 7
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::int
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::int AS month_days
),
bm AS (
  SELECT m.variable_pool_yen AS spendable_yen
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
explicit AS (
  SELECT
    c.category,
    ROUND(c.monthly_limit_yen::numeric * b.period_days / b.month_days, 0)::int AS period_limit_yen
  FROM budget_month_categories c, bounds b
  WHERE c.year_month = b.ym
),
range_spent AS (
  SELECT
    COALESCE(e.category, 'その他') AS category,
    SUM(e.amount_yen) AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
  GROUP BY 1
)
SELECT
  x.category AS "大分類",
  x.period_limit_yen AS "予算",
  COALESCE(s.spent_yen, 0) AS "使用額",
  x.period_limit_yen - COALESCE(s.spent_yen, 0) AS "残予算"
FROM explicit x
LEFT JOIN range_spent s ON s.category = x.category
ORDER BY "残予算" ASC;

パネル 4g: 大分類の消化率（Gauge、半円）¶

未使用＝弧が空、使い込むほど弧が伸びる、にするため 数値は消化率%（0〜100）にする。残予算は下のラベルに出す（中央は消化率）。

設定（Gauge の目盛り）¶

Visualization: Gauge
Format: Table
Value options → Show: All values
Standard options → Min: 0 / Max: 100（必須） Max を空（Auto）にすると、系列の最大値（例: 41.9）が満タンになる。しきい値 80% は「Max の 80%」なので、Auto だと 41.9×0.8≈33.5 超で赤になる。
Thresholds: 絶対値で 80（または Absolute / Percentage を確認）。消化率なら 80 = 80% Percentage モードにしきい値 80 を入れると「スケールの 80%」になり、Max=Auto と組み合わさって混乱が増す。Max=100 + しきい値は Absolute の 80 が安全である。

SQL（`${period}` 対応）¶

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1
      WHEN '日次' THEN 1
      WHEN 'week' THEN 7
      WHEN '週次' THEN 7
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::int
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::int AS month_days
),
bm AS (
  SELECT m.variable_pool_yen AS spendable_yen
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
explicit AS (
  SELECT
    c.category,
    ROUND(c.monthly_limit_yen::numeric * b.period_days / b.month_days, 0)::int AS period_limit_yen
  FROM budget_month_categories c, bounds b
  WHERE c.year_month = b.ym
),
range_spent AS (
  SELECT
    COALESCE(e.category, 'その他') AS category,
    SUM(e.amount_yen) AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
  GROUP BY 1
)
SELECT
  x.category
    || E'\n残り '
    || (x.period_limit_yen - COALESCE(s.spent_yen, 0))::text
    || '円' AS "ラベル",
  ROUND(
    100.0 * COALESCE(s.spent_yen, 0) / NULLIF(x.period_limit_yen, 0),
    1
  ) AS "消化率"
FROM explicit x
LEFT JOIN range_spent s ON s.category = x.category
ORDER BY "消化率" DESC NULLS LAST;

パネル 4b: 食費の今日目安（Stat）¶

Bot と同じロジックの近似。

週枠（月曜始まり）= 週開始時点の月残り × 今週の月内日数 ÷ 週開始から月末までの日数
今朝の今日枠:
週に余裕 → （週枠 − 今日より前の今週使用）÷ 今週の残り日数
週超過済み → 週枠 ÷ 今週の日数（週の赤字は今日枠に載せない）
表示 = 今日枠 − 今日の食費

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    ((date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month')::date - 1) AS month_end,
    (NOW() AT TIME ZONE 'Asia/Tokyo')::date AS today,
    date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') AS day_start,
    date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day' AS day_end,
    date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS week_start
),
bounds2 AS (
  SELECT
    b.*,
    GREATEST(b.week_start, b.ym) AS week_spend_start,
    LEAST(b.week_start + 6, b.month_end) AS week_end_in_month,
    (b.month_end - GREATEST(b.week_start, b.ym)) + 1 AS days_to_month_end
  FROM bounds b
),
food AS (
  SELECT monthly_limit_yen AS limit_yen
  FROM budget_month_categories
  WHERE year_month = (SELECT ym FROM bounds2) AND category = '食費'
),
spent AS (
  SELECT
    COALESCE(SUM(amount_yen) FILTER (
      WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.ym
        AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.day_end
    ), 0) AS spent_month,
    COALESCE(SUM(amount_yen) FILTER (
      WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.week_spend_start
        AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.day_end
    ), 0) AS spent_week,
    COALESCE(SUM(amount_yen) FILTER (
      WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.day_start
        AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.day_end
    ), 0) AS spent_today
  FROM expenses e, bounds2 b
  WHERE COALESCE(e.category, '') = '食費'
),
calc AS (
  SELECT
    b.*,
    s.spent_month,
    s.spent_week,
    s.spent_today,
    CASE
      WHEN (f.limit_yen - (s.spent_month - s.spent_week)) <= 0 THEN 0
      ELSE (f.limit_yen - (s.spent_month - s.spent_week))
             * GREATEST((b.week_end_in_month - b.week_spend_start) + 1, 1)
             / GREATEST(b.days_to_month_end, 1)
    END AS weekly,
    (s.spent_week - s.spent_today) AS spent_before_today,
    GREATEST((b.week_end_in_month - b.today) + 1, 1) AS days_left,
    GREATEST((b.week_end_in_month - b.week_spend_start) + 1, 1) AS days_in_week
  FROM food f CROSS JOIN spent s CROSS JOIN bounds2 b
)
SELECT
  (
    CASE
      WHEN (c.weekly - c.spent_before_today) >= 0
        THEN (c.weekly - c.spent_before_today) / c.days_left
      WHEN c.weekly > 0
        THEN c.weekly / c.days_in_week
      ELSE 0
    END
    - c.spent_today
  ) AS food_daily_left_yen
FROM calc c;

パネル 5: 今月予算ヘッダ（Table）¶

WITH bounds AS (
  SELECT date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym
),
explicit_sum AS (
  SELECT COALESCE(SUM(monthly_limit_yen), 0) AS yen
  FROM budget_month_categories c, bounds b
  WHERE c.year_month = b.ym
)
SELECT
  m.year_month AS "月",
  m.status AS "状態",
  m.ceiling_yen AS "上限(前月給与)",
  m.savings_yen AS "固定貯金",
  m.fixed_costs_yen AS "固定費",
  m.variable_pool_yen AS "変動枠",
  e.yen AS "変動カテゴリ割当合計",
  m.unallocated_to_surplus_yen AS "未割当→別枠",
  m.month_end_surplus_yen AS "月末実残→別枠"
FROM budget_months m
CROSS JOIN bounds b
CROSS JOIN explicit_sum e
WHERE m.year_month = b.ym;

パネル 5b: 固定費マスタ（Table）¶

SELECT name AS "固定費", monthly_yen AS "月額", expense_category AS "実績カテゴリ", active AS "有効"
FROM fixed_cost_items
ORDER BY name;

パネル 5c: 別枠貯金残高（Stat）¶

SELECT COALESCE(SUM(amount_yen), 0) AS surplus_balance_yen
FROM surplus_ledger;

パネル 5d: 別枠貯金の月次入金（Table）¶

SELECT
  year_month AS "月",
  reason AS "理由",
  amount_yen AS "金額",
  note AS "メモ",
  created_at AT TIME ZONE 'Asia/Tokyo' AS "計上日時"
FROM surplus_ledger
ORDER BY created_at DESC
LIMIT 50;

パネル 6〜: 収入まわり（`incomes`）¶

収入テーブルが空だと 0 になる（その場合パネル1/3は計画月収フォールバック）。 Pie なら Value options を All values に。

6a. 今月の記録収入合計（Stat）¶

SELECT COALESCE(SUM(amount_yen), 0) AS recorded_income_yen
FROM incomes
WHERE (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      >= date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
  AND (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      <  date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month';

6b. 計画月収 vs 記録収入（Table）¶

WITH recorded AS (
  SELECT COALESCE(SUM(amount_yen), 0) AS recorded_income_yen
  FROM incomes
  WHERE (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
        >= date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    AND (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
        <  date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
)
SELECT
  COALESCE((SELECT monthly_income_yen FROM budget_settings WHERE id = 1), 0) AS "計画月収",
  r.recorded_income_yen AS "記録収入",
  r.recorded_income_yen
    - COALESCE((SELECT monthly_income_yen FROM budget_settings WHERE id = 1), 0) AS "差分(記録-計画)"
FROM recorded r;

6c. （廃止）全体の残り¶

パネル 3 と同じ。新規ダッシュボードではパネル 3 を使う。

6d. 収入の分類内訳（Pie / Table）¶

SELECT
  COALESCE(category, 'その他') AS category,
  SUM(amount_yen) AS amount
FROM incomes
WHERE (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      >= date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
  AND (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      <  date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
GROUP BY 1
ORDER BY amount DESC;

Pie のときは Show = All values。

6e. 今月の収支サマリ（Table）¶

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') AS month_start,
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month' AS month_end
),
inc AS (
  SELECT COALESCE(SUM(i.amount_yen), 0) AS income_yen
  FROM incomes i, bounds b
  WHERE (COALESCE(i.received_at, i.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.month_start
    AND (COALESCE(i.received_at, i.created_at) AT TIME ZONE 'Asia/Tokyo') < b.month_end
),
exp AS (
  SELECT COALESCE(SUM(e.amount_yen), 0) AS expense_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.month_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.month_end
)
SELECT
  i.income_yen AS "収入",
  e.expense_yen AS "支出",
  i.income_yen - e.expense_yen AS "収支",
  (SELECT monthly_savings_yen FROM budget_settings WHERE id = 1) AS "予定貯金",
  i.income_yen - e.expense_yen
    - (SELECT monthly_savings_yen FROM budget_settings WHERE id = 1) AS "貯金後の残り感"
FROM inc i
CROSS JOIN exp e;

6f. 最近の収入明細（Table）¶

SELECT
  id AS income_id,
  COALESCE(received_at, created_at) AS when_ts,
  amount_yen,
  category,
  counterparty,
  memo,
  source
FROM incomes
ORDER BY id DESC
LIMIT 30;

参考: 分析用（サブダッシュボード向け）¶

SELECT COALESCE(SUM(amount_yen), 0) AS today_total
FROM expenses
WHERE COALESCE(paid_at, created_at)::date
    = (NOW() AT TIME ZONE 'Asia/Tokyo')::date;

右上の時間ピッカーと連動させたい分析パネルでは $__timeFrom() / $__timeTo() を WHERE に書く。予算パネルは「今月」固定なので、通常はマクロ不要である。

うまくいった状態¶

「変動枠の残り」「ジャンル別の残り」「別枠貯金」が見える
固定費マスタと変動カテゴリが分かれている
Bot が動いている

次¶

予算ダッシュボードが安定したら Tasker + Tunnel（PayPay 自動） へ進む。