年間収益率をベースにモニタリングするアイディア

Published:

はじめに

活用方法

調査方法

抽出項目

SQLサンプル

WITH sys AS (
  SELECT 
    "public"."SYSTEM_DATE"."system_date" AS "system_date",
    ROW_NUMBER() OVER (ORDER BY "public"."SYSTEM_DATE"."created_at" DESC) AS rn
  FROM "public"."SYSTEM_DATE"
),
dates AS (
  SELECT
    MAX(CASE WHEN rn = 1 THEN system_date END) AS current_date,
    MAX(CASE WHEN rn = 2 THEN system_date END) AS prev_week_date,
    MAX(CASE WHEN rn = 4 THEN system_date END) AS prev_4week_date
  FROM sys
),
year_first_last AS (
  SELECT
    p.code,
    EXTRACT(YEAR FROM TO_DATE(p.date, 'YYYY-MM-DD')) AS year,
    MAX(CASE WHEN rn_asc = 1 THEN p.open END)  AS open_first,
    MAX(CASE WHEN rn_desc = 1 THEN p.close END) AS close_last
  FROM (
    SELECT
      code,
      date,
      open,
      close,
      ROW_NUMBER() OVER (
        PARTITION BY code, EXTRACT(YEAR FROM TO_DATE(date, 'YYYY-MM-DD'))
        ORDER BY TO_DATE(date, 'YYYY-MM-DD') ASC
      )  AS rn_asc,
      ROW_NUMBER() OVER (
        PARTITION BY code, EXTRACT(YEAR FROM TO_DATE(date, 'YYYY-MM-DD'))
        ORDER BY TO_DATE(date, 'YYYY-MM-DD') DESC
      ) AS rn_desc
    FROM "public"."prices"
  ) p
  GROUP BY p.code, year
),
pivoted AS (
  SELECT 
    p.code,
    -- 直近系
    MAX(CASE WHEN p.date = d.current_date THEN p.close END) AS close_latest,
    MAX(CASE WHEN p.date = d.prev_week_date THEN p.close END) AS close_week,
    MAX(CASE WHEN p.date = d.prev_4week_date THEN p.close END) AS close_month,
    -- 年初・年末系（銘柄ごと）
    MAX(CASE WHEN y.year = 2025 THEN y.open_first END)  AS open_2025,
    MAX(CASE WHEN y.year = 2024 THEN y.open_first END)  AS open_2024,
    MAX(CASE WHEN y.year = 2024 THEN y.close_last END)  AS close_2024,
    MAX(CASE WHEN y.year = 2023 THEN y.open_first END)  AS open_2023,
    MAX(CASE WHEN y.year = 2023 THEN y.close_last END)  AS close_2023
  FROM "public"."prices" p
  CROSS JOIN dates d
  LEFT JOIN year_first_last y
    ON p.code = y.code
  GROUP BY p.code
)
SELECT
  pv.code,
  b.name,
  b.market,
  b.sector_33,
  pv.close_latest,
  pv.close_week,
  pv.close_month,
  pv.open_2025,
  pv.open_2024, pv.close_2024,
  pv.open_2023, pv.close_2023,
  ROUND((pv.close_latest / pv.open_2025 - 1) * 100, 2) AS yoy_2025,
  ROUND((pv.close_2024 / pv.open_2024 - 1) * 100, 2) AS yoy_2024,
  ROUND((pv.close_2023 / pv.open_2023 - 1) * 100, 2) AS yoy_2023,
  ROUND((pv.close_latest / pv.close_week - 1) * 100, 2) AS wow,
  ROUND((pv.close_latest / pv.close_month - 1) * 100, 2) AS mom
FROM pivoted pv
JOIN "public"."Brands" b ON pv.code = b.code

今日時点のリストを眺めて考察

TODO