# 维度表
CREATE TABLE DIM_DATE (
DATE_KEY INTEGER NOT NULL,
DATE_VALUE DATE NOT NULL,
YEAR INTEGER,
QUARTER INTEGER,
MONTH INTEGER,
MONTH_NAME VARCHAR(20),
DAY_OF_MONTH INTEGER,
DAY_OF_WEEK INTEGER,
CONSTRAINT PK_DIM_DATE PRIMARY KEY (DATE_KEY)
) ORGANIZE BY COLUMN;

CREATE TABLE DIM_PRODUCT (
PRODUCT_KEY INTEGER NOT NULL,
PRODUCT_ID VARCHAR(50) NOT NULL,
PRODUCT_NAME VARCHAR(200) NOT NULL,
CATEGORY VARCHAR(100),
BRAND VARCHAR(100),
CONSTRAINT PK_DIM_PRODUCT PRIMARY KEY (PRODUCT_KEY)
) ORGANIZE BY COLUMN;

CREATE TABLE DIM_CUSTOMER (
CUSTOMER_KEY INTEGER NOT NULL,
CUSTOMER_ID VARCHAR(50) NOT NULL,
CUSTOMER_NAME VARCHAR(200) NOT NULL,
CITY VARCHAR(100),
PROVINCE VARCHAR(100),
SEGMENT VARCHAR(50),
CONSTRAINT PK_DIM_CUSTOMER PRIMARY KEY (CUSTOMER_KEY)
) ORGANIZE BY COLUMN;

# 事实表
CREATE TABLE FACT_SALES (
DATE_KEY INTEGER NOT NULL,
PRODUCT_KEY INTEGER NOT NULL,
CUSTOMER_KEY INTEGER NOT NULL,
QUANTITY INTEGER,
TOTAL_AMOUNT DECIMAL(18, 2),
PROFIT DECIMAL(18, 2),
CONSTRAINT PK_FACT_SALES PRIMARY KEY (DATE_KEY, PRODUCT_KEY, CUSTOMER_KEY)
) ORGANIZE BY COLUMN
PARTITION BY RANGE (DATE_KEY)
(
STARTING 20260101 ENDING 20261231 EVERY 1 MONTH
);

# 按月聚合MQT
CREATE TABLE MQT_SALES_MONTH AS (
SELECT
d.YEAR,
d.MONTH,
p.CATEGORY,
SUM(f.QUANTITY) AS TOTAL_QUANTITY,
SUM(f.TOTAL_AMOUNT) AS TOTAL_AMOUNT,
SUM(f.PROFIT) AS TOTAL_PROFIT
FROM FACT_SALES f
INNER JOIN DIM_DATE d ON f.DATE_KEY = d.DATE_KEY
INNER JOIN DIM_PRODUCT p ON f.PRODUCT_KEY = p.PRODUCT_KEY
GROUP BY d.YEAR, d.MONTH, p.CATEGORY
) DATA INITIALLY DEFERRED REFRESH DEFERRED
ORGANIZE BY COLUMN;

REFRESH TABLE MQT_SALES_MONTH;

# 按客户聚合MQT
CREATE TABLE MQT_SALES_CUSTOMER AS (
SELECT
c.CUSTOMER_KEY,
c.CUSTOMER_NAME,
c.SEGMENT,
SUM(f.TOTAL_AMOUNT) AS TOTAL_AMOUNT,
COUNT(*) AS ORDER_COUNT
FROM FACT_SALES f
INNER JOIN DIM_CUSTOMER c ON f.CUSTOMER_KEY = c.CUSTOMER_KEY
GROUP BY c.CUSTOMER_KEY, c.CUSTOMER_NAME, c.SEGMENT
) DATA INITIALLY DEFERRED REFRESH DEFERRED
ORGANIZE BY COLUMN;

REFRESH TABLE MQT_SALES_CUSTOMER;

# 排名函数
SELECT
PRODUCT_NAME,
TOTAL_AMOUNT,
RANK() OVER (ORDER BY TOTAL_AMOUNT DESC) AS SALES_RANK,
DENSE_RANK() OVER (ORDER BY TOTAL_AMOUNT DESC) AS DENSE_RANK,
ROW_NUMBER() OVER (ORDER BY TOTAL_AMOUNT DESC) AS ROW_NUM
FROM (
SELECT
p.PRODUCT_NAME,
SUM(f.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM FACT_SALES f
INNER JOIN DIM_PRODUCT p ON f.PRODUCT_KEY = p.PRODUCT_KEY
GROUP BY p.PRODUCT_NAME
);

# 聚合函数
SELECT
PRODUCT_NAME,
TOTAL_AMOUNT,
SUM(TOTAL_AMOUNT) OVER (PARTITION BY CATEGORY) AS CATEGORY_TOTAL,
AVG(TOTAL_AMOUNT) OVER (PARTITION BY CATEGORY) AS CATEGORY_AVG,
TOTAL_AMOUNT * 100.0 / SUM(TOTAL_AMOUNT) OVER (PARTITION BY CATEGORY) AS PERCENTAGE
FROM (
SELECT
p.PRODUCT_NAME,
p.CATEGORY,
SUM(f.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM FACT_SALES f
INNER JOIN DIM_PRODUCT p ON f.PRODUCT_KEY = p.PRODUCT_KEY
GROUP BY p.PRODUCT_NAME, p.CATEGORY
);

# 移动窗口函数
SELECT
DATE_VALUE,
DAILY_SALES,
AVG(DAILY_SALES) OVER (
ORDER BY DATE_VALUE
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS MOVING_AVG_7DAY,
SUM(DAILY_SALES) OVER (
ORDER BY DATE_VALUE
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS MOVING_SUM_7DAY
FROM (
SELECT
d.DATE_VALUE,
SUM(f.TOTAL_AMOUNT) AS DAILY_SALES
FROM FACT_SALES f
INNER JOIN DIM_DATE d ON f.DATE_KEY = d.DATE_KEY
GROUP BY d.DATE_VALUE
);

# LAG/LEAD函数
SELECT
DATE_VALUE,
DAILY_SALES,
LAG(DAILY_SALES, 1) OVER (ORDER BY DATE_VALUE) AS PREV_DAY_SALES,
LAG(DAILY_SALES, 7) OVER (ORDER BY DATE_VALUE) AS PREV_WEEK_SALES,
LEAD(DAILY_SALES, 1) OVER (ORDER BY DATE_VALUE) AS NEXT_DAY_SALES
FROM (
SELECT
d.DATE_VALUE,
SUM(f.TOTAL_AMOUNT) AS DAILY_SALES
FROM FACT_SALES f
INNER JOIN DIM_DATE d ON f.DATE_KEY = d.DATE_KEY
GROUP BY d.DATE_VALUE
);

# CUBE多维分析
SELECT
COALESCE(d.YEAR, ‘ALL’) AS YEAR,
COALESCE(d.MONTH, ‘ALL’) AS MONTH,
COALESCE(p.CATEGORY, ‘ALL’) AS CATEGORY,
SUM(f.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM FACT_SALES f
INNER JOIN DIM_DATE d ON f.DATE_KEY = d.DATE_KEY
INNER JOIN DIM_PRODUCT p ON f.PRODUCT_KEY = p.PRODUCT_KEY
GROUP BY CUBE(d.YEAR, d.MONTH, p.CATEGORY);

# ROLLUP层次分析
SELECT
COALESCE(c.PROVINCE, ‘ALL’) AS PROVINCE,
COALESCE(c.CITY, ‘ALL’) AS CITY,
SUM(f.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM FACT_SALES f
INNER JOIN DIM_CUSTOMER c ON f.CUSTOMER_KEY = c.CUSTOMER_KEY
GROUP BY ROLLUP(c.PROVINCE, c.CITY);

# GROUPING SETS自定义分组
SELECT
COALESCE(d.YEAR, ‘ALL’) AS YEAR,
COALESCE(p.CATEGORY, ‘ALL’) AS CATEGORY,
COALESCE(c.SEGMENT, ‘ALL’) AS SEGMENT,
SUM(f.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM FACT_SALES f
INNER JOIN DIM_DATE d ON f.DATE_KEY = d.DATE_KEY
INNER JOIN DIM_PRODUCT p ON f.PRODUCT_KEY = p.PRODUCT_KEY
INNER JOIN DIM_CUSTOMER c ON f.CUSTOMER_KEY = c.CUSTOMER_KEY
GROUP BY GROUPING SETS (
(d.YEAR, p.CATEGORY),
(d.YEAR, c.SEGMENT),
(p.CATEGORY, c.SEGMENT)
);

# JDBC连接字符串
jdbc:db2://hostname:50000/FGEDB:currentSchema=BI;

# ODBC配置
# 在/etc/odbcinst.ini中添加：
[DB2]
Description = IBM DB2 ODBC Driver
Driver = /opt/ibm/db2/V12.1/lib64/libdb2o.so
FileUsage = 1

# 在/etc/odbc.ini中添加：
[FGEDB]
Description = FGEDB Database
Driver = DB2
Database = FGEDB
Servername = hostname
Port = 50000

# 测试连接
isql -v FGEDB username password

# Python连接示例
import ibm_db

conn_str = “DATABASE=FGEDB;HOSTNAME=hostname;PORT=50000;PROTOCOL=TCPIP;UID=username;PWD=password;”
conn = ibm_db.connect(conn_str, ”, ”)

sql = “SELECT * FROM FACT_SALES FETCH FIRST 10 ROWS ONLY”
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_both(stmt)
while result:
print(result)
result = ibm_db.fetch_both(stmt)

ibm_db.close(conn)

# Tableau连接
# 1. 选择”IBM DB2″数据源
# 2. 输入服务器、端口、数据库名称
# 3. 输入用户名和密码
# 4. 选择Schema和表
# 5. 创建数据提取或实时连接

# Power BI连接
# 1. 选择”IBM DB2数据库”数据源
# 2. 输入服务器和数据库名称
# 3. 选择连接模式（导入或DirectQuery）
# 4. 输入凭据
# 5. 选择表并加载数据

# 创建视图简化BI工具访问
CREATE VIEW BI_SALES_SUMMARY AS
SELECT
d.DATE_VALUE,
d.YEAR,
d.MONTH,
d.DAY_NAME,
p.PRODUCT_NAME,
p.CATEGORY,
p.BRAND,
c.CUSTOMER_NAME,
c.CITY,
c.PROVINCE,
c.SEGMENT,
f.QUANTITY,
f.TOTAL_AMOUNT,
f.PROFIT
FROM FACT_SALES f
INNER JOIN DIM_DATE d ON f.DATE_KEY = d.DATE_KEY
INNER JOIN DIM_PRODUCT p ON f.PRODUCT_KEY = p.PRODUCT_KEY
INNER JOIN DIM_CUSTOMER c ON f.CUSTOMER_KEY = c.CUSTOMER_KEY;

# 授权BI用户
GRANT SELECT ON BI_SALES_SUMMARY TO bi_user;
GRANT SELECT ON MQT_SALES_MONTH TO bi_user;
GRANT SELECT ON MQT_SALES_CUSTOMER TO bi_user;