Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@akihiro_suto(Akihiro Suto)

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

Last updated at Posted at 2023-07-29

はじめに

簡単なサンプルデータが欲しいときってありますよね😊サンプルデータをいれた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);

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  |
2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?