SQL、Python、Power Query、DAX用の軽いサンプルデータ作成クエリ😘

# はじめに

簡単なサンプルデータが欲しいときってありますよね😊サンプルデータをいれたServerをたたくとか、そもそもサンプルデータを入れたテンプレートファイルつくっとけとか、いろいろ方法はありますが、サクっとサンプルデータが入ったテーブルを作成したいときってありますよね！？

記録しておき、いつでも流用できるようにしておきます。

## サンプルデータ

下記の3テーブルを作成するためのクエリを記述していきます。

Customersテーブル

| CustomerID | FirstName | LastName | Email                      |
|------------|-----------|----------|----------------------------|
| 1          | 太郎      | 山田     | taro.yamada@example.com     |
| 2          | 花子      | 佐藤     | hanako.sato@example.com     |
| 3          | 健太      | 鈴木     | kenta.suzuki@example.com    |
| 4          | 美香      | 高橋     | mika.takahashi@example.com  |

Productsテーブル

| ProductID | ProductName      | UnitPrice |
|-----------|------------------|-----------|
| 101       | 和風クッキー       | 1000      |
| 102       | 抹茶ラテ         | 800       |
| 103       | さくらんぼのスイーツ | 1200      |
| 104       | 手作りようかん       | 1500      |

Ordersテーブル

| OrderID | CustomerID | ProductID | Quantity | OrderDate   |
|---------|------------|-----------|----------|-------------|
| 1001    | 1          | 101       | 2        | 2023-07-10  |
| 1002    | 2          | 102       | 1        | 2023-07-11  |
| 1003    | 1          | 103       | 3        | 2023-07-12  |
| 1004    | 3          | 101       | 5        | 2023-07-15  |
| 1005    | 4          | 104       | 1        | 2023-07-16  |


## SQL

```Customers.sql
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100)
);

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, N'太郎', N'山田', 'taro.yamada@example.com'),
    (2, N'花子', N'佐藤', 'hanako.sato@example.com'),
    (3, N'健太', N'鈴木', 'kenta.suzuki@example.com'),
    (4, N'美香', N'高橋', 'mika.takahashi@example.com');
```

```Products.sql
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    UnitPrice DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, UnitPrice)
VALUES
    (101, N'和風クッキー', 1000),
    (102, N'抹茶ラテ', 800),
    (103, N'さくらんぼのスイーツ', 1200),
    (104, N'手作りようかん', 1500);
```

```sql
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    OrderDate DATE
);

INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate)
VALUES
    (1001, 1, 101, 2, '2023-07-10'),
    (1002, 2, 102, 1, '2023-07-11'),
    (1003, 1, 103, 3, '2023-07-12'),
    (1004, 3, 101, 5, '2023-07-15'),
    (1005, 4, 104, 1, '2023-07-16');
```

## Python

```Customers.py
import pandas as pd

# Customersテーブルを作成
customers_data = {
    'CustomerID': [1, 2, 3, 4],
    'FirstName': ['太郎', '花子', '健太', '美香'],
    'LastName': ['山田', '佐藤', '鈴木', '高橋'],
    'Email': ['taro.yamada@example.com', 'hanako.sato@example.com', 'kenta.suzuki@example.com', 'mika.takahashi@example.com']
}
df_customers = pd.DataFrame(customers_data)
```

```Products.py
import pandas as pd

# Productテーブルを作成
products_data = {
    'ProductID': [101, 102, 103, 104],
    'ProductName': ['和風クッキー', '抹茶ラテ', 'さくらんぼのスイーツ', '手作りようかん'],
    'UnitPrice': [1000, 800, 1200, 1500]
}
df_product = pd.DataFrame(products_data)
```

```Orders.py
import pandas as pd

# Ordersテーブルを作成
orders_data = {
    'OrderID': [1001, 1002, 1003, 1004, 1005],
    'CustomerID': [1, 2, 1, 3, 4],
    'ProductID': [101, 102, 103, 101, 104],
    'Quantity': [2, 1, 3, 5, 1],
    'OrderDate': ['2023-07-10', '2023-07-11', '2023-07-12', '2023-07-15', '2023-07-16']
}
df_order = pd.DataFrame(orders_data)
```

## Power Query

```Customers.pq
let
    Source = Table.FromRecords({
        [CustomerID = 1, FirstName = "太郎", LastName = "山田", Email = "taro.yamada@example.com"],
        [CustomerID = 2, FirstName = "花子", LastName = "佐藤", Email = "hanako.sato@example.com"],
        [CustomerID = 3, FirstName = "健太", LastName = "鈴木", Email = "kenta.suzuki@example.com"],
        [CustomerID = 4, FirstName = "美香", LastName = "高橋", Email = "mika.takahashi@example.com"]
    },type table[CustomerID = Number.Type, FirstName = Text.Type, LastName = Text.Type, Email = Text.Type])
in
    Source
```

```Products.pq
let
    Source = Table.FromRecords({
        [ProductID = 101, ProductName = "和風な商品A", UnitPrice = 1000],
        [ProductID = 102, ProductName = "抹茶ラテ", UnitPrice = 800],
        [ProductID = 103, ProductName = "さくらんぼのスイーツ", UnitPrice = 1200],
        [ProductID = 104, ProductName = "手作り和菓子", UnitPrice = 1500]
    },type table[ProductID = Number.Type, ProductName = Text.Type, UnitPrice = Number.Type])
in
    Source
```

```Orders.pq
let
    Source = Table.FromRecords({
        [OrderID = 1001, CustomerID = 1, ProductID = 101, Quantity = 2, OrderDate = #date(2023, 7, 10)],
        [OrderID = 1002, CustomerID = 2, ProductID = 102, Quantity = 1, OrderDate = #date(2023, 7, 11)],
        [OrderID = 1003, CustomerID = 1, ProductID = 103, Quantity = 3, OrderDate = #date(2023, 7, 12)],
        [OrderID = 1004, CustomerID = 3, ProductID = 101, Quantity = 5, OrderDate = #date(2023, 7, 15)],
        [OrderID = 1005, CustomerID = 4, ProductID = 104, Quantity = 1, OrderDate = #date(2023, 7, 16)]
    },type table[OrderID = Number.Type, CustomerID = Number.Type, ProductID = Number.Type, Quantity = Number.Type, OrderDate = Date.Type])
in
    Source
```

## DAX

```Customers.dax
Customers = DATATABLE (
    "CustomerID", INTEGER,
    "FirstName", STRING,
    "LastName", STRING,
    "Email", STRING,
    {
        { 1, "太郎", "山田", "taro.yamada@example.com" },
        { 2, "花子", "佐藤", "hanako.sato@example.com" },
        { 3, "健太", "鈴木", "kenta.suzuki@example.com" },
        { 4, "美香", "高橋", "mika.takahashi@example.com" }
    }
)
```

```Products.dax
Products = DATATABLE (
    "ProductID", INTEGER,
    "ProductName", STRING,
    "UnitPrice", CURRENCY,
    {
        { 101, "和風クッキー", 1000 },
        { 102, "抹茶ラテ", 800 },
        { 103, "さくらんぼのスイーツ", 1200 },
        { 104, "手作りようかん", 1500 }
    }
)
```

```Orders.dax
Orders = DATATABLE (
    "OrderID", INTEGER,
    "CustomerID", INTEGER,
    "ProductID", INTEGER,
    "Quantity", INTEGER,
    "OrderDate", DATE,
    {
        { 1001, 1, 101, 2, DATE(2023, 7, 10) },
        { 1002, 2, 102, 1, DATE(2023, 7, 11) },
        { 1003, 1, 103, 3, DATE(2023, 7, 12) },
        { 1004, 3, 101, 5, DATE(2023, 7, 15) },
        { 1005, 4, 104, 1, DATE(2023, 7, 16) }
    }
)
```

## Markdown

```Customers.md
| CustomerID | FirstName | LastName | Email                      |
|------------|-----------|----------|----------------------------|
| 1          | 太郎      | 山田     | taro.yamada@example.com     |
| 2          | 花子      | 佐藤     | hanako.sato@example.com     |
| 3          | 健太      | 鈴木     | kenta.suzuki@example.com    |
| 4          | 美香      | 高橋     | mika.takahashi@example.com  |
```

```Products.md
| ProductID | ProductName      | UnitPrice |
|-----------|------------------|-----------|
| 101       | 和風クッキー       | 1000      |
| 102       | 抹茶ラテ         | 800       |
| 103       | さくらんぼのスイーツ | 1200      |
| 104       | 手作りようかん       | 1500      |
```

```Orders.md
| OrderID | CustomerID | ProductID | Quantity | OrderDate   |
|---------|------------|-----------|----------|-------------|
| 1001    | 1          | 101       | 2        | 2023-07-10  |
| 1002    | 2          | 102       | 1        | 2023-07-11  |
| 1003    | 1          | 103       | 3        | 2023-07-12  |
| 1004    | 3          | 101       | 5        | 2023-07-15  |
| 1005    | 4          | 104       | 1        | 2023-07-16  |
```