CREATE TABLE sales_data ( 店舗名 VARCHAR(50), 月次目標額 NUMERIC, 年月 DATE, クォーター VARCHAR(10), 会計年度 INT, 売上金額 NUMERIC );
INSERT INTO sales_data (店舗名, 月次目標額, 年月, クォーター, 会計年度, 売上金額) SELECT '店舗' || s.id AS 店舗名, ROUND((RANDOM() * 1000000) + 500000) AS 月次目標額, -- 500,000円から1,500,000円のランダムな目標額 d.date AS 年月, CASE WHEN EXTRACT(MONTH FROM d.date) IN (4, 5, 6) THEN '1Q' WHEN EXTRACT(MONTH FROM d.date) IN (7, 8, 9) THEN '2Q' WHEN EXTRACT(MONTH FROM d.date) IN (10, 11, 12) THEN '3Q' WHEN EXTRACT(MONTH FROM d.date) IN (1, 2, 3) THEN '4Q' END AS クォーター, 2023 AS 会計年度, ROUND(RANDOM() * 2000000) AS 売上金額 FROM generate_series(1, 5) AS s(id), -- 5店舗を生成 generate_series('2023-04-01'::date, '2024-03-01', '1 month') AS d(date);
INSERT INTO sales_data (店舗名, 月次目標額, 年月, クォーター, 会計年度, 売上金額) SELECT '店舗' || s.id AS 店舗名, ROUND((RANDOM() * 1000000) + 500000) AS 月次目標額, -- 500,000円から1,500,000円のランダムな目標額 d.date AS 年月, CASE WHEN EXTRACT(MONTH FROM d.date) IN (4, 5, 6) THEN '1Q' WHEN EXTRACT(MONTH FROM d.date) IN (7, 8, 9) THEN '2Q' WHEN EXTRACT(MONTH FROM d.date) IN (10, 11, 12) THEN '3Q' WHEN EXTRACT(MONTH FROM d.date) IN (1, 2, 3) THEN '4Q' END AS クォーター, 2023 AS 会計年度, ROUND(RANDOM() * 2000000) AS 売上金額 FROM generate_series(1, 5) AS s(id), -- 5店舗を生成 generate_series('2023-04-01'::date, '2024-03-01', '1 month') AS d(date);
UPDATE sales_data SET 店舗名 = CASE 店舗名 WHEN '店舗1' THEN 'A店' WHEN '店舗2' THEN 'B店' WHEN '店舗3' THEN 'C店' WHEN '店舗4' THEN 'D店' WHEN '店舗5' THEN 'E店' END WHERE 店舗名 IN ('店舗1', '店舗2', '店舗3', '店舗4', '店舗5');
■正規表現
正規表現パターン
検査するパターン: 上３桁がアルファベットで、2桁目が「0」、その後に6桁の数字が続く予算コードを検出する。
置換するパターン: 2桁目の「0」を「O」に置換する。
正規表現と置換式の例
検査する正規表現: [A-Za-z]0[A-Za-z]\d{6}
[A-Za-z]は任意のアルファベット1文字を表し、0はその後の文字が0であることを示し、さらに[A-Za-z]はもう1文字のアルファベットを表します。\d{6}は6桁の数字を表します。
置換処理: マッチした文字列の2桁目の「0」を「O」に置換します。これを行うために、マッチングした部分の前後をキャプチャしておき、置換時に利用します。
Input: 処理する文字列（例: 予算コード）
Pattern:
([A-Za-z])0([A-Za-z]\d{6})
ここで()はキャプチャグループを作成し、置換の際に再利用します。
Replacement: $1O$2
$1は最初のキャプチャグループ（最初のアルファベット）、Oは置換後の文字、$2は2つ目のキャプチャグループ（アルファベットと6桁の数字）を表します。
この設定を使うことで、指定されたパターンに一致する予算コードのうち、誤って「0」が入力された箇所を「O」に修正することができます。この方法は、コードの誤入力を効率的に修正するのに非常に有効です。
■ウィンドウ関数を使用する場合
SELECT product_code, date, sales, initial_inventory - SUM(sales) OVER (PARTITION BY product_code ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_inventory FROM inventory_data ORDER BY product_code, date;
■ウィンドウ関数を使用しない場合
SELECT t1.product_code, t1.date, t1.sales, initial_inventory - ( SELECT SUM(t2.sales) FROM inventory_data t2 WHERE t2.product_code = t1.product_code AND t2.date <= t1.date ) AS current_inventory FROM inventory_data t1 ORDER BY t1.product_code, t1.date;
CREATE TABLE inventory_data ( id SERIAL PRIMARY KEY, product_code VARCHAR(255) NOT NULL, date DATE NOT NULL, sales INT NOT NULL, initial_inventory INT NOT NULL ); -- 商品コードごとに初期在庫と売上データを挿入 INSERT INTO inventory_data (product_code, date, sales, initial_inventory) VALUES ('P1001', '2023-01-01', 5, 100), ('P1001', '2023-01-02', 3, 100), ('P1001', '2023-01-03', 2, 100), ('P1002', '2023-01-01', 7, 200), ('P1002', '2023-01-02', 4, 200), ('P1002', '2023-01-03', 1, 200), -- 他の商品コードや日付についても同様にデータを追加 ('P1003', '2023-01-01', 6, 150), ('P1003', '2023-01-02', 3, 150), ('P1003', '2023-01-03', 4, 150);
ELECT
m.3桁コード,
IIF(d.key2 IS NULL, "", d.key2) AS key2,
IIF(d.key3 IS NULL, "", d.key3) AS key3,
IIF(d.code IS NULL, "", d.code) AS code,
IIF(d.amount IS NULL, 0, d.amount) AS amount
FROM
マスタテーブル m
LEFT JOIN
詳細テーブル d
ON
m.3桁コード = d.key3;SELECT `予算コード`, `年月`, `前年生産残`, `受注高`, `生産高`, `前年生産残` + SUM(`受注高`) OVER (PARTITION BY `予算コード` ORDER BY `年月`) - SUM(`生産高`) OVER (PARTITION BY `予算コード` ORDER BY `年月`) AS `生産残` FROM あなたのテーブル名 ORDER BY `予算コード`, `年月`;
Sub ConvertDataAndWriteToSheetV2_WithAdditionalColumns() Dim wsSource As Worksheet, wsDest As Worksheet Dim lr As Long, i As Long, j As Long, destRow As Long Dim threeDigitCode As String, productSymbol As String, businessSymbol As String Dim itemCategory As String Dim yearValue As Long, monthValue As String, monthNum As Integer Dim dateString As String ' ソースシート設定 Set wsSource = ThisWorkbook.Sheets("ソースシート名") ' ソースシート名を適宜変更 ' 変換後シート作成 Set wsDest = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) wsDest.Name = "変換後データV2" ' ヘッダー設定 With wsDest .Cells(1, 1).Value = "業務番号" .Cells(1, 2).Value = "3桁コード" .Cells(1, 3).Value = "製品記号" .Cells(1, 4).Value = "業務記号" .Cells(1, 5).Value = "年月" .Cells(1, 6).Value = "年" .Cells(1, 7).Value = "月" .Cells(1, 8).Value = "項目" .Cells(1, 9).Value = "金額" .Cells(1, 10).Value = "更新日" .Cells(1, 11).Value = "処理年度" End With lr = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row destRow = 2 For i = 3 To lr For j = 20 To 55 ' T列からBC列 ' 新たに追加された列の値を設定 threeDigitCode = Left(wsSource.Cells(i, 1).Value, 3) productSymbol = Mid(wsSource.Cells(i, 1).Value, 2, 1) businessSymbol = Mid(wsSource.Cells(i, 1).Value, 3, 1) ' 項目の抽出 If InStr(wsSource.Cells(2, j).Value, "受注") > 0 Then itemCategory = "受注高" ElseIf InStr(wsSource.Cells(2, j).Value, "%") > 0 Then itemCategory = "割合" ElseIf InStr(wsSource.Cells(2, j).Value, "生産") > 0 Then itemCategory = "生産高" End If ' 月と年の抽出 monthValue = Mid(wsSource.Cells(2, j).Value, 1, InStr(1, wsSource.Cells(2, j).Value, "月") - 1) monthNum = CInt(monthValue) yearValue = Val(wsSource.Cells(i, 57).Value) ' BE列の値 = 処理年度 If monthNum <= 3 Then yearValue = yearValue + 1 dateString = Format(DateSerial(yearValue, monthNum, 1), "yyyy-mm-dd") ' データを変換後シートに書き込み With wsDest .Cells(destRow, 1).Value = wsSource.Cells(i, 1).Value .Cells(destRow, 2).Value = threeDigitCode .Cells(destRow, 3).Value = productSymbol .Cells(destRow, 4).Value = businessSymbol .Cells(destRow, 5).Value = dateString .Cells(destRow, 6).Value = yearValue .Cells(destRow, 7).Value = monthNum .Cells(destRow, 8).Value = itemCategory .Cells(destRow, 9).Value = wsSource.Cells(i, j).Value .Cells(destRow, 10).Value = wsSource.Cells(i, 56).Value ' 更新日 .Cells(destRow, 11).Value = wsSource.Cells(i, 57).Value ' 処理年度 destRow = destRow + 1 End With Next j Next i End Sub
