背景
ほんと、どうでもいい記録
会社でなんか相談が上がってたんで、久々に Power Query/BI を触ってみるかと思った記録
概要
- 製造データと不良品データを突き合わせて、製造日を出したい。のだと思われる。(よく知らない
- その際、製造データが大量にあり過ぎるので、out of memory になるとかなんとか
Sample Data 生成
欲しいイメージはこんな感じ
まずは、Power Query で動的生成
久々にやってみたけど、Serial Number の継続が意外に面倒だった。
SerialMap, DateMap として更新していったけど、もっと良い方法がありそうな気がして困る
適当に生成してみる例
let
// 基本設定: 生成行数
rowCount = 100000,
// 基本設定: マシーンタイプ
machineTypes = {"Type1", "Type2", "Type3", "Type4", "Type5"},
startDate = #date(2024, 4, 1),
// 初期化
initialState = [
SerialMap = [Type1 = 1, Type2 = 1, Type3 = 1, Type4 = 1, Type5 = 1],
DateMap = [Type1 = startDate, Type2 = startDate, Type3 = startDate, Type4 = startDate, Type5 = startDate],
Index = 0,
newRow = [
beginSerial = null,
endSerial = null,
machineType = null,
productDate = null
]
],
// 各行を生成
generateRows = List.Generate(
() => initialState,
each [Index] < rowCount,
each [
// ランダムに productNumber を生成
productNumber = Number.RoundDown(Number.RandomBetween(1, 30)),
// ランダムに machineType を選択
machineType = machineTypes{Number.RoundDown(Number.RandomBetween(0, 5))},
// 現在の serialNumber と日付を取得
currentSerial = Record.Field([SerialMap], machineType),
currentDate = Record.Field([DateMap], machineType),
// SerialMap を更新
SerialMap = Record.TransformFields([SerialMap], {{machineType, each _ + productNumber}}),
// DateMap を更新
DateMap = Record.TransformFields([DateMap], {{machineType, each _ + #duration(1, 0, 0, 0)}}),
// インデックスを増加
Index = [Index] + 1,
// 新しい行を返す
newRow = [
beginSerial = currentSerial,
endSerial = currentSerial + productNumber - 1,
machineType = machineType,
productDate = currentDate
]
],
each [newRow]
),
// List を Table に変換
validRows = List.RemoveFirstN(generateRows, 1),
resultTable = Table.FromRecords(validRows, {"beginSerial", "endSerial", "machineType", "productDate"}),
// 型を変更
changedTypes = Table.TransformColumnTypes(resultTable, {{"beginSerial", Int64.Type}, {"endSerial", Int64.Type}, {"machineType", type text}, {"productDate", type date}})
in
changedTypes
念の為 C# 版も
Power Query でやるとなんか別の影響出ちゃうかもと重い、csv 生成用に用意してみた
適当に生成してみる例
class Program
{
static void Main()
{
string filename = GenerateFilename();
SaveFileDialogAndOutputCsv(filename);
}
// ファイル名生成メソッド
private static string GenerateFilename()
{
return DateTime.Now.ToString("yyyyMMdd_") + "machineData.csv";
}
// CSV出力メソッド
private static void SaveFileDialogAndOutputCsv(string filename)
{
var saveFileDialog = new Microsoft.Win32.SaveFileDialog
{
Title = "保存先を指定してください",
InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory),
Filter = "CSVファイル(*.csv)|*.csv",
FileName = filename
};
if (saveFileDialog.ShowDialog() == true)
{
using (var writer = new StreamWriter(saveFileDialog.FileName, false, Encoding.UTF8))
{
writer.WriteLine("machineType,beginSerial,endSerial,productDate");
GenerateAndWriteData(writer);
}
}
}
// データ生成とCSV書き込みメソッド
private static void GenerateAndWriteData(StreamWriter writer)
{
string[] machineTypes = { "Type1", "Type2", "Type3", "Type4", "Type5" };
DateTime startDate = new DateTime(2024, 4, 1);
Random random = new Random();
foreach (var type in machineTypes)
{
int serial = 1;
for (int i = 0; i < 500000; i++)
{
var productNumber = random.Next(1, 10);
string productDate = startDate.AddDays(i).ToString("yyyy/MM/dd");
writer.WriteLine($"{type},{serial},{serial + productNumber - 1},{productDate}");
serial += productNumber;
}
}
}
}
そうそう、不良データも
イメージはこんな感じ
こっちは、確認用に serial を変えたかったんで、Dax で用意。ええんかな?
不良データ
DefectTable_csv =
VAR machineTypes = {"Type1", "Type2", "Type3", "Type4", "Type5"}
VAR productSerial = GENERATESERIES(100, 500, 1)
RETURN
GENERATE(
SELECTCOLUMNS(
machineTypes,
"machineType", [Value]
),
TOPN(1,
SELECTCOLUMNS(
productSerial,
"productSerial", [Value]
), RAND())
)
で、実際に、抽出を Dax で
苦手なまま、勉強出来ていない Dax に挑戦
読み込んだ不良データに、列追加版
列追加
defectDate =
CALCULATE(
FIRSTDATE('productLog'[productDate]),
FILTER(
'productLog',
'productLog'[machineType] = EARLIER([machineType])
&& 'productLog'[beginSerial] <= EARLIER([productSerial])
&& 'productLog'[endSerial] >= EARLIER([productSerial])
)
)
Table として作成版
テーブル追加版
ViewTable_csv =
ADDCOLUMNS(
DefectTable_csv,
"productDate",
CALCULATE(
MIN(productCsv[productDate]),
FILTER(
productCsv,
productCsv[machineType] = DefectTable_csv[machineType]
&& productCsv[beginSerial] <= DefectTable_csv[productSerial]
&& productCsv[endSerial] >= DefectTable_csv[productSerial]
)
)
)
あとがき
そもそも、以下にしても状況再現っぽいことが出来なかったので、諦めた
諦めたけど、少しは努力したので、記録しておくのである
- power query で 100万行
- csv で、250万行