はじめに
簡単なサンプルデータが欲しいときってありますよね😊サンプルデータをいれたServerをたたくとか、そもそもサンプルデータを入れたテンプレートファイルつくっとけとか、いろいろ方法はありますが、サクっとサンプルデータが入ったテーブルを作成したいときってありますよね！？
記録しておき、いつでも流用できるようにしておきます。
サンプルデータ
下記の3テーブルを作成するためのクエリを記述していきます。
Customersテーブル
| CustomerID | FirstName | LastName | |
|---|---|---|---|
| 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);
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 |