SQL演習課題

6. 日次リターン

bigqueryの解答

WITH DateConverted AS (
  SELECT
    PARSE_DATE('%Y-%m-%d', Date) AS Date,
    Code,
    Market_capitalization
  FROM
    `kenshu2024.dbo.リサ運_研修_PqOriMkt_M`
),

PreviousDay AS (
  SELECT
    a.Date,
    a.Code,
    a.Market_capitalization
 AS Close_Today,
    b.Market_capitalization AS Close_Yesterday
  FROM
    DateConverted a
  LEFT JOIN
    DateConverted b
  ON
    a.Code = b.Code AND
    DATE_SUB(a.Date, INTERVAL 1 DAY) = b.Date
)

SELECT
  Date,
  Code,
  Close_Today,
  Close_Yesterday,
  ((Close_Today - Close_Yesterday) / Close_Yesterday) AS Daily_Return
FROM
  PreviousDay
ORDER BY
  Code;

SQLserverの解答

WITH DateConverted AS (
  SELECT
    CONVERT(date, [Date]) AS [Date],  -- 日付の変換
    Code,
    Market_capitalization
  FROM
    [kenshu2024].[dbo].[リサ運_研修_PqOriMkt_M]
),

PreviousDay AS (
  SELECT
    a.[Date],
    a.Code,
    a.Market_capitalization AS Close_Today,
    b.Market_capitalization AS Close_Yesterday
  FROM
    DateConverted a
  LEFT JOIN
    DateConverted b
  ON
    a.Code = b.Code AND
    DATEADD(day, -1, a.[Date]) = b.[Date]  -- 1日前の日付を取得
)

SELECT
  [Date],
  Code,
  Close_Today,
  Close_Yesterday,
  ((Close_Today - Close_Yesterday) / NULLIF(Close_Yesterday, 0)) AS Daily_Return  -- ゼロ除算のエラーを防ぐためNULLIFを使用
FROM
  PreviousDay
ORDER BY
  Code;

7. 加重平均リターン

bigqueryの解答

-- テンポラリ結果を保持するCTE
WITH DateConverted AS (
  SELECT
    PARSE_DATE('%Y-%m-%d', Date) AS Date,
    Code,
    Market_capitalization
  FROM
    `kenshu2024.dbo.リサ運_研修_PqOriMkt_M`
),

PreviousDay AS (
  SELECT
    a.Date,
    a.Code,
    a.Market_capitalization AS Close_Today,
    b.Market_capitalization AS Close_Yesterday
  FROM
    DateConverted a
  LEFT JOIN
    DateConverted b
  ON
    a.Code = b.Code AND
    DATE_SUB(a.Date, INTERVAL 1 DAY) = b.Date
),

Returns AS (
  SELECT
    Date,
    Code,
    Close_Today,
    Close_Yesterday,
    ((Close_Today - Close_Yesterday) / Close_Yesterday) AS Daily_Return
  FROM
    PreviousDay
),

WeightedReturns AS (
  SELECT
    FORMAT_DATE('%Y-%m', Date) AS Month,
    Code,
    SUM(Close_Yesterday * Daily_Return) AS Weighted_Return_Sum,
    SUM(Close_Yesterday) AS Close_Yesterday_Sum
  FROM
    Returns
  GROUP BY
    Month, Code
)

-- 月ごとの加重平均リターンを計算
SELECT
  Month,
  Code,
  (Weighted_Return_Sum / Close_Yesterday_Sum) AS Monthly_Weighted_Avg_Return
FROM
  WeightedReturns
ORDER BY
  Month, Code;

SQLsererの解答

-- テンポラリ結果を保持するCTE
WITH DateConverted AS (
  SELECT
    CONVERT(date, [Date], 120) AS [Date],  -- 日付のフォーマット変換
    Code,
    Market_capitalization
  FROM
    [kenshu2024].[dbo].[リサ運_研修_PqOriMkt_M]
),

PreviousDay AS (
  SELECT
    a.[Date],
    a.Code,
    a.Market_capitalization AS Close_Today,
    b.Market_capitalization AS Close_Yesterday
  FROM
    DateConverted a
  LEFT JOIN
    DateConverted b
  ON
    a.Code = b.Code AND
    DATEADD(day, -1, a.[Date]) = b.[Date]  -- 前日の日付を計算
),

Returns AS (
  SELECT
    [Date],
    Code,
    Close_Today,
    Close_Yesterday,
    (CAST(Close_Today AS float) - Close_Yesterday) / Close_Yesterday AS Daily_Return  -- 日次リターンを計算
  FROM
    PreviousDay
),

WeightedReturns AS (
  SELECT
    FORMAT([Date], 'yyyy-MM') AS Month,  -- 月のフォーマット
    Code,
    SUM(Close_Yesterday * Daily_Return) AS Weighted_Return_Sum,
    SUM(Close_Yesterday) AS Close_Yesterday_Sum
  FROM
    Returns
  GROUP BY
    FORMAT([Date], 'yyyy-MM'), Code
)

-- 月ごとの加重平均リターンを計算
SELECT
  Month,
  Code,
  Weighted_Return_Sum / Close_Yesterday_Sum AS Monthly_Weighted_Avg_Return
FROM
  WeightedReturns
ORDER BY
  Month, Code;

10．移動平均

bigqueryの解答

# 移動平均に関するもの
WITH DateConverted AS (
    SELECT
        PARSE_DATE('%Y-%m-%d', Date) AS Date,
        Code,
        Market_capitalization
    FROM `kenshu2024.dbo.リサ運_研修_PqOriMkt_M`
)

SELECT
  Code,
  Date,
  COALESCE(AVG(Market_capitalization) OVER(
    ORDER BY Date
    ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING)
) AS move_average
FROM `kenshu2024.dbo.リサ運_研修_PqOriMkt_M`
WHERE Code = 45680
ORDER BY Date

SQL-serverの解答

WITH DateConverted AS (
    SELECT
        CONVERT(date, [Date], 111) AS [Date],  -- 日付フォーマットが 'YYYY/MM/DD' の場合
        [Code],
        [Market_capitalization]
    FROM [kenshu2024].[dbo].[リサ運_研修_PqOriMkt_M]
)

SELECT
    [Code],
    [Date],
    AVG([Market_capitalization]) OVER (
        PARTITION BY [Code]
        ORDER BY [Date]
        ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING
    ) AS move_average
FROM DateConverted
WHERE [Code] = 45680
ORDER BY [Date];