本篇會製作出一個大型的資料庫，裏面的銷售明細資料表多達1000 萬筆資料。

底下解說如何快速新增資料、如何快速查詢，最後製作樞紐分析表。

安裝套件

pip install mysql-python-connector openpyxl xlrd

SDK

在專案下新增 SDK 目錄，然後新增 sql.py 檔，輸入如下代碼

import mysql.connector as mysql
class sql():
    @staticmethod
    def conn():
        try:
            return mysql.connect(
                 host = "localhost",
                 user = "帳號",
                 password = "密碼",
                 database = "cashier"
            )
        except Exception as e:
            return None

員工資料

請由如下代碼新增 “員工資料“

use cashier;
CREATE TABLE `員工資料` (
  `id` int NOT NULL AUTO_INCREMENT,
  `員工編號` varchar(10) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `姓名` varchar(20) COLLATE utf8mb3_unicode_ci NOT NULL,
  `身份証` varchar(20) COLLATE utf8mb3_unicode_ci NOT NULL,
  `性別` varchar(1) COLLATE utf8mb3_unicode_ci NOT NULL,
  `生日` date NOT NULL,
  `地址` varchar(45) COLLATE utf8mb3_unicode_ci NOT NULL,
  `部門` varchar(15) COLLATE utf8mb3_unicode_ci NOT NULL,
  `電話` varchar(15) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `帳號` varchar(20) COLLATE utf8mb3_unicode_ci NOT NULL,
  `密碼` varchar(15) COLLATE utf8mb3_unicode_ci NOT NULL,
  `就職日` date DEFAULT NULL,
  `離職日` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `帳號_UNIQUE` (`帳號`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

然後新增 10 筆資料

廠商資料

請由如下代碼新增 “廠商資料“

use cashier;
CREATE TABLE `廠商資料` (
  `id` int NOT NULL AUTO_INCREMENT,
  `廠商統編` varchar(10) COLLATE utf8mb3_unicode_ci NOT NULL,
  `名稱` varchar(20) COLLATE utf8mb3_unicode_ci NOT NULL,
  `地址` varchar(45) COLLATE utf8mb3_unicode_ci NOT NULL,
  `電話` varchar(15) COLLATE utf8mb3_unicode_ci NOT NULL,
  `負責人` varchar(10) COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

然後新增 5 筆資料

商品資料

請由如下代碼新增 “商品資料“

use cashier;
CREATE TABLE `商品資料` (
  `id` int NOT NULL AUTO_INCREMENT,
  `商品編號` varchar(13) COLLATE utf8mb3_unicode_ci NOT NULL,
  `品名` varchar(20) COLLATE utf8mb3_unicode_ci NOT NULL,
  `規格` varchar(45) COLLATE utf8mb3_unicode_ci NOT NULL,
  `成本價` int NOT NULL,
  `售價` int NOT NULL,
  `廠商統編` varchar(10) COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

然後新增 10 筆資料

銷售單號

請由如下代碼新增 “銷售單號“

use cashier;
CREATE TABLE `銷售單號` (
  `id` int NOT NULL AUTO_INCREMENT,
  `銷售單號` varchar(15) COLLATE utf8mb3_unicode_ci NOT NULL,
  `日期` date NOT NULL,
  `時間` time NOT NULL,
  `員工編號` varchar(10) COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `銷售單號_UNIQUE` (`銷售單號`),
  KEY `日期` (`日期`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

銷售明細

請由如下代碼新增 “銷售明細“

use cashier;
CREATE TABLE `銷售明細` (
  `id` int NOT NULL AUTO_INCREMENT,
  `銷售單號` varchar(15) COLLATE utf8mb3_unicode_ci NOT NULL,
  `商品編號` varchar(10) COLLATE utf8mb3_unicode_ci NOT NULL,
  `數量` int NOT NULL,
  `售價` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `銷售單號` (`銷售單號`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

關聯式資料表 – 商品資料view

在 view 中新增 “商品資料view”，然後輸入如下代碼

use cashier;
CREATE VIEW 商品資料view AS
select
a.商品編號 as 商品編號,
a.品名 as 品名,
a.規格 as 規格,
a.成本價 as 成本價,
a.售價 as 售價,
b.名稱 as 名稱,
b.地址 as 地址,
b.電話 as 電話
from 商品資料 a
join 廠商資料 b on (a.廠商統編=b.廠商統編)

關聯式資料表 – 銷售明細View

在 view 中新增 “銷售明細view”，然後輸入如下代碼

use cashier;
CREATE VIEW 銷售明細view AS
select 
a.銷售單號 as 銷售單號,
a.日期 as 日期,
a.時間 as 時間,
b.商品編號 as 商品編號,
d.品名 as 品名,
d.規格 as 規格,
d.售價 as 預定售價,
d.成本價 as 成本價,
b.數量 as 數量,
b.售價 as 售價,
b.數量*b.售價 as 小計,
a.員工編號 as 員工編號,
c.姓名 as 姓名

from 銷售單號 a 
join 銷售明細 b on (a.銷售單號 = b.銷售單號)
join 員工資料 c on (a.員工編號=c.員工編號)
join 商品資料 d on (b.商品編號=d.商品編號)

新增銷售單號及銷售明細 1000 萬筆資料

銷售單號及銷售明細表各有 1000 萬筆資料，請使用如下代碼新增。

import datetime
import random
import time
from MahalSdk.sql import sql

def randomtime():
    return str(random.random() * (datetime.timedelta(0, 86400))).split(".")[0]

epochs=10
batch=1_000_000

dates=[]
years=[2020,2021,2022,2023,2024]
for i in range(epochs*batch):
    pay_year=years[random.randint(0,len(years)-1)]
    start_date=datetime.datetime(pay_year, 1, 1)
    target=(start_date+datetime.timedelta(days=random.randint(0,364))).strftime("%Y-%m-%d")
    dates.append(target)

dates=sorted(dates)
times=[randomtime() for _ in range(epochs*batch)]

conn=sql.conn()
cursor=conn.cursor()
cursor.execute("truncate table 銷售單號")
cursor.execute("truncate table 銷售明細")

cursor.execute("select * from 商品資料")
rs=cursor.fetchall()

for e in range(epochs):
    print(f"epoch : {e+1}")
    sales=[]
    for i in range(batch):
        sale_no = int(time.time() * 100000)+i
        pay_day=dates[e*batch+i]
        pay_time=times[e*batch+i]
        id = random.randint(1, 10)
        sales.append([sale_no, pay_day, pay_time, f'{id:04d}'])

    cmd="insert into 銷售單號 (銷售單號, 日期, 時間, 員工編號) values (%s, %s, %s, %s)"
    cursor.executemany(cmd, sales)
    conn.commit()

    data=[]
    for i in range(batch):
        no=random.randint(1,10)#商品編號
        qty=random.randint(1,5)
        price=rs[no-1][5]
        data.append([sales[i][0],f'{no:04d}', qty, price])
    cmd = "insert into 銷售明細 (銷售單號, 商品編號, 數量, 售價) values (%s, %s, %s, %s)"
    cursor.executemany(cmd, data)
    conn.commit()

conn.close()

查詢筆數

使用如下指令可以查詢銷售明細共有幾筆資料

use cashier;
select count(*) from 銷售明細;

結果:
10000000

查詢銷售明細

使用如下 SQL 語法，可查詢 2024/01 所有的銷售明細

select a.銷售單號, a.日期, a.時間, c.品名, b.數量, b.售價, d.姓名
from( 
	select * from 銷售單號 where 日期 >='2024-01-01' and 日期 < '2024-02-01' 
)a
join 銷售明細 b on (a.銷售單號=b.銷售單號)
join 商品資料 c on b.商品編號 = c.商品編號
join 員工資料 d on a.員工編號=d.員工編號

亦可使用如下查詢銷售明細view 表，速度比較慢。

use cashier;
select * from 銷售明細view where 日期 >='2024-01-01' and 日期 < '2024-02-01'

SQL 製作樞紐分析表

底下使用 SQL 語法從銷售明細view 表產生 2024 年的樞紐分析表，時間約 64 秒。

import time

import pandas as pd

from MahalSdk.sql import sql
conn=sql.conn()
cursor=conn.cursor()
cmd="""
select 日期,
sum(case when 商品編號 = '0001' then 數量 else 0 end) as 科學麵,
sum(case when 商品編號 = '0002' then 數量 else 0 end) as 台灣啤酒,
sum(case when 商品編號 = '0003' then 數量 else 0 end) as 可樂果,
sum(case when 商品編號 = '0004' then 數量 else 0 end) as 魷魚絲, 
sum(case when 商品編號 = '0005' then 數量 else 0 end) as 話匣子, 
sum(case when 商品編號 = '0006' then 數量 else 0 end) as 布丁, 
sum(case when 商品編號 = '0007' then 數量 else 0 end) as 牛肉麵, 
sum(case when 商品編號 = '0008' then 數量 else 0 end) as 來一客, 
sum(case when 商品編號 = '0009' then 數量 else 0 end) as 烏骨雞, 
sum(case when 商品編號 = '0010' then 數量 else 0 end) as 黑松汽水, 
format(sum(售價*數量), 0) as 總價  
from 銷售明細view 
where 日期>='2024-01-01' and 日期 <='2024-12-31'  
group by 日期 
order by 日期
"""
t1=time.time()
cursor.execute(cmd)
columns=[d[0] for d in cursor.description]
rs=cursor.fetchall()
df=pd.DataFrame(data=rs, columns=columns)
t2=time.time()
print(df)
print(f"花費時間 : {t2-t1}秒")

Pandas 製作樞紐分析表

底下是銷售明細view 表撈出 2024 年的資料，再由 Pandas 製作樞紐分析表，約需 66 秒。

import pandas as pd
import time
from MahalSdk.sql import sql

conn=sql.conn()
cursor=conn.cursor()
t1=time.time()
cursor.execute("select * from 銷售明細view where 日期>='2024-01-01' and 日期 <='2024-12-31'")
rs=cursor.fetchall()
columns=[d[0] for d in cursor.description]
df=pd.DataFrame(data=rs, columns=columns)
table=df.pivot_table(index=['日期'], columns=['品名'], values=['數量'],fill_value="",aggfunc='sum')
t2=time.time()
print(table)
print(f"花費時間 : {t2-t1}秒")
table.to_excel("total.xlsx", sheet_name="Sheet1")

大型資料庫