尧图网页设计 
-- -- 1. 环境准备创建数据库并切换上下文 -- IF DB_ID(JewelryGlobalDB) IS NULL BEGIN CREATE DATABASE JewelryGlobalDB; END GO USE JewelryGlobalDB; GO -- -- 2. 表结构定义 (DDL) -- -- 2.1 全球门店表 (Stores) CREATE TABLE dbo.GlobalStores ( StoreId INT IDENTITY(1,1) PRIMARY KEY, StoreName NVARCHAR(200) NOT NULL, CountryCode CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 Region NVARCHAR(50) NOT NULL, -- e.g., EMEA, APAC, Americas CurrencyCode CHAR(3) NOT NULL, -- 本地结算货币 TimeZone NVARCHAR(50) NOT NULL, -- IANA Time Zone Name OpenDate DATE NOT NULL ); -- 2.2 全球产品目录 (Products) CREATE TABLE dbo.GlobalProducts ( ProductId INT IDENTITY(1,1) PRIMARY KEY, BrandLine NVARCHAR(50) NOT NULL, -- e.g., Tiffany Co., Cartier Category NVARCHAR(50) NOT NULL, -- e.g., Diamond Rings, Gold Chains MaterialType NVARCHAR(50) NOT NULL, -- e.g., Platinum 950, 18K Rose Gold BasePriceUSD DECIMAL(18,2) NOT NULL, -- 统一基准定价 (USD) WeightGrams DECIMAL(10,2) NULL -- 重量用于黄金/铂金类计算 ); -- 2.3 全球销售订单 (SalesOrders) CREATE TABLE dbo.GlobalSalesOrders ( OrderId INT IDENTITY(1,1) PRIMARY KEY, StoreId INT NOT NULL, OrderDateUTC DATETIMEOFFSET(0) NOT NULL, -- 存储 UTC0 时间 CustomerId INT NULL, -- 允许匿名购买 LocalAmount DECIMAL(18,2) NOT NULL, -- 交易发生时的本地金额 LocalCurrency CHAR(3) NOT NULL, -- 交易货币 ExchangeRate DECIMAL(10,6) NOT NULL, -- 当日汇率 (1 USD ? Local) Status NVARCHAR(20) NOT NULL -- Completed, Cancelled, Returned ); -- 2.4 订单明细 (OrderItems) - 用于精确到SKU的销售分析 CREATE TABLE dbo.OrderItems ( OrderItemId INT IDENTITY(1,1) PRIMARY KEY, OrderId INT NOT NULL, ProductId INT NOT NULL, Quantity INT NOT NULL, UnitPriceLocal DECIMAL(18,2) NOT NULL, -- 单品本地单价 LineAmountLocal DECIMAL(18,2) NOT NULL -- 行总额 ); -- 2.5 全球客户 (Customers) CREATE TABLE dbo.GlobalCustomers ( CustomerId INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(100), LastName NVARCHAR(100), MembershipTier NVARCHAR(20) NOT NULL, -- Silver, Gold, Platinum, Black HomeCountry CHAR(2) NULL, Email NVARCHAR(200) NULL, RegisterDate DATETIMEOFFSET(0) NOT NULL ); -- 2.6 全球员工 (Employees) CREATE TABLE dbo.GlobalEmployees ( EmployeeId INT IDENTITY(1,1) PRIMARY KEY, FullName NVARCHAR(100) NOT NULL, Department NVARCHAR(50) NOT NULL, -- Retail, Manufacturing, Logistics, Corporate CountryCode CHAR(2) NOT NULL, Position NVARCHAR(50) NOT NULL, SalaryLocal DECIMAL(18,2) NOT NULL, SalaryCurrency CHAR(3) NOT NULL ); -- -- 3. 外键约束 (FK) -- ALTER TABLE dbo.GlobalSalesOrders ADD CONSTRAINT FK_Orders_Store FOREIGN KEY (StoreId) REFERENCES dbo.GlobalStores(StoreId); ALTER TABLE dbo.GlobalSalesOrders ADD CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerId) REFERENCES dbo.GlobalCustomers(CustomerId); ALTER TABLE dbo.OrderItems ADD CONSTRAINT FK_Items_Order FOREIGN KEY (OrderId) REFERENCES dbo.GlobalSalesOrders(OrderId); ALTER TABLE dbo.OrderItems ADD CONSTRAINT FK_Items_Product FOREIGN KEY (ProductId) REFERENCES dbo.GlobalProducts(ProductId); -- -- 4. 示例数据插入 (DML) -- -- 4.1 插入门店数据 (覆盖主要市场) INSERT INTO dbo.GlobalStores (StoreName, CountryCode, Region, CurrencyCode, TimeZone, OpenDate) VALUES (New York Fifth Avenue, US, Americas, USD, America/New_York, 2010-05-15), (Paris Place Vendôme, FR, EMEA, EUR, Europe/Paris, 2012-09-01), (Tokyo Ginza, JP, APAC, JPY, Asia/Tokyo, 2015-03-20), (Shanghai Nanjing Road, CN, APAC, CNY, Asia/Shanghai, 2018-11-11), (London Bond Street, GB, EMEA, GBP, Europe/London, 2011-06-01); -- 4.2 插入产品数据 INSERT INTO dbo.GlobalProducts (BrandLine, Category, MaterialType, BasePriceUSD, WeightGrams) VALUES (Tiffany Co., Diamond Rings, Platinum 950, 12000.00, NULL), (Tiffany Co., Necklaces, 18K Yellow Gold, 3500.00, 15.50), (Cartier, Bracelets, 18K Rose Gold, 8500.00, 25.00), (Chopard, Watches, Steel Diamonds, 25000.00, NULL), (Bulgari, Rings, 18K White Gold, 4200.00, 8.00); -- 4.3 插入客户数据 INSERT INTO dbo.GlobalCustomers (FirstName, LastName, MembershipTier, HomeCountry, Email, RegisterDate) VALUES (John, Smith, Gold, US, john.smithexample.com, 2023-01-10 10:00:00 00:00), (Marie, Dubois, Platinum, FR, marie.duboisexample.com, 2022-05-20 14:30:00 00:00), (Kenji, Tanaka, Silver, JP, kenji.tanakaexample.com, 2024-02-14 09:15:00 00:00), (Wei, Zhang, Black, CN, wei.zhangexample.com, 2021-11-11 20:00:00 00:00), (Sarah, Connor, Gold, GB, sarah.connorexample.com, 2023-06-01 11:00:00 00:00); -- 4.4 插入员工数据 INSERT INTO dbo.GlobalEmployees (FullName, Department, CountryCode, Position, SalaryLocal, SalaryCurrency) VALUES (Alice Johnson, Retail, US, Store Manager, 120000.00, USD), (Pierre Martin, Retail, FR, Sales Associate, 45000.00, EUR), (Yuki Sato, Manufacturing, JP, Gem Setter, 6000000.00, JPY), (Li Wei, Retail, CN, Store Manager, 300000.00, CNY), (James Brown, Logistics, GB, Warehouse Lead, 55000.00, GBP); -- 4.5 插入销售订单 (注意汇率模拟时间为2026年) -- 假设汇率: 1 USD 0.92 EUR, 1 USD 148 JPY, 1 USD 7.2 CNY, 1 USD 1.27 GBP INSERT INTO dbo.GlobalSalesOrders (StoreId, OrderDateUTC, CustomerId, LocalAmount, LocalCurrency, ExchangeRate, Status) VALUES (1, 2026-09-15 14:30:00 -04:00, 1, 12000.00, USD, 1.000000, Completed), -- US Store, USD (2, 2026-09-16 10:00:00 02:00, 2, 11040.00, EUR, 0.920000, Completed), -- FR Store, EUR (11040 * 0.92 10156.8 USD approx) (3, 2026-09-17 15:00:00 09:00, 3, 1776000.00, JPY, 148.000000, Completed), -- JP Store, JPY (4, 2026-09-18 11:00:00 08:00, 4, 86400.00, CNY, 7.200000, Completed), -- CN Store, CNY (5, 2026-09-19 16:00:00 01:00, 5, 15200.00, GBP, 1.270000, Completed), -- UK Store, GBP (1, 2026-09-20 09:00:00 -04:00, NULL, 3500.00, USD, 1.000000, Completed); -- Walk-in customer -- 4.6 插入订单明细 INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPriceLocal, LineAmountLocal) VALUES (1, 1, 1, 12000.00, 12000.00), (2, 3, 1, 11040.00, 11040.00), (3, 2, 1, 1776000.00, 1776000.00), (4, 4, 1, 86400.00, 86400.00), (5, 1, 1, 15200.00, 15200.00), (6, 2, 1, 3500.00, 3500.00); -- -- 5. 性能优化索引 -- -- 加速按区域和日期范围查询 CREATE INDEX IX_Orders_DateRegion ON dbo.GlobalSalesOrders (OrderDateUTC, StoreId); -- 加速按客户查询历史订单 CREATE INDEX IX_Orders_Customer ON dbo.GlobalSalesOrders (CustomerId, OrderDateUTC); -- 加速按产品类别查询 CREATE INDEX IX_Items_Product ON dbo.OrderItems (ProductId); -- -- 6. 验证数据 (可选运行) -- SELECT s.StoreName, o.LocalCurrency, COUNT(o.OrderId) AS OrderCount FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId GROUP BY s.StoreName, o.LocalCurrency ORDER BY OrderCount DESC; -- 快速验证计算各区域的总销售额 (USD) SELECT s.Region, SUM(o.LocalAmount * o.ExchangeRate) AS TotalSalesUSD FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId WHERE o.Status Completed GROUP BY s.Region ORDER BY TotalSalesUSD DESC; -- -- 1. 全球区域 x 品牌销售汇总 -- SELECT COALESCE(s.Region, GLOBAL TOTAL) AS Region, COALESCE(p.BrandLine, ALL BRANDS) AS BrandLine, -- 聚合指标 COUNT(DISTINCT o.OrderId) AS GlobalOrderCount, SUM(o.LocalAmount * o.ExchangeRate) AS SalesUSD, AVG(o.LocalAmount * o.ExchangeRate) AS AvgTicketUSD, -- 统计活跃国家数 COUNT(DISTINCT s.CountryCode) AS ActiveCountries FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId JOIN dbo.OrderItems oi ON o.OrderId oi.OrderId JOIN dbo.GlobalProducts p ON oi.ProductId p.ProductId WHERE o.Status Completed AND o.OrderDateUTC 2026-09-01 -- 限定最近一个月 AND o.OrderDateUTC 2026-10-01 GROUP BY GROUPING SETS ( (s.Region, p.BrandLine), -- 明细区域 x 品牌 (s.Region), -- 小计区域总计 (p.BrandLine), -- 小计品牌总计 () -- 总计全球总计 ) ORDER BY GROUPING(s.Region), -- 先排有值的区域 s.Region, GROUPING(p.BrandLine), -- 再排有值的品牌 p.BrandLine; -- -- 2. 高净值客户 (HNI) 行为分析 (修正版) -- WITH CustomerMetrics AS ( -- 第一步计算每个客户的个人指标 SELECT c.CustomerId, c.MembershipTier, c.HomeCountry, SUM(o.LocalAmount * o.ExchangeRate) AS TotalSpendUSD, COUNT(o.OrderId) AS OrderCount, MAX(o.OrderDateUTC) AS LastPurchaseDate FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalCustomers c ON c.CustomerId o.CustomerId WHERE c.MembershipTier IN (Platinum, Black) AND o.Status Completed GROUP BY c.CustomerId, c.MembershipTier, c.HomeCountry ), CustomerBehavior AS ( -- 第二步衍生指标计算 SELECT CustomerId, MembershipTier, HomeCountry, TotalSpendUSD, OrderCount, LastPurchaseDate, DATEDIFF(day, LastPurchaseDate, GETUTCDATE()) AS DaysSinceLastPurchase, CASE WHEN DATEDIFF(day, LastPurchaseDate, GETUTCDATE()) 90 THEN At Risk ELSE Active END AS RetentionStatus FROM CustomerMetrics ) -- 第三步最终汇总展示 SELECT COALESCE(MembershipTier, GLOBAL TOTAL) AS Tier, COALESCE(HomeCountry, ALL COUNTRIES) AS Country, COUNT(CustomerId) AS UniqueHighNetWorthCustomers, SUM(TotalSpendUSD) AS TotalContributionUSD, ROUND(AVG(TotalSpendUSD), 2) AS AvgSpendPerCustomerUSD, ROUND(AVG(OrderCount), 2) AS AvgOrdersPerCustomer, SUM(CASE WHEN RetentionStatus At Risk THEN 1 ELSE 0 END) AS AtRiskCount, SUM(CASE WHEN RetentionStatus Active THEN 1 ELSE 0 END) AS ActiveCount, -- 生成一个整数用于排序层级越高数字越大 GROUPING_ID(MembershipTier, HomeCountry) AS GroupingLevel FROM CustomerBehavior GROUP BY GROUPING SETS ( (MembershipTier, HomeCountry), -- 明细 (MembershipTier), -- 等级小计 (HomeCountry), -- 国家小计 () -- 总计 ) HAVING SUM(TotalSpendUSD) 1000 -- 过滤低贡献数据 ORDER BY GroupingLevel DESC, -- 先排总计再排小计最后排明细 MembershipTier ASC, -- 同级内按会员等级排序 Country ASC; -- 最后按国家排序 -- -- 3. 材质与品类销售分析 (ERP) -- SELECT p.MaterialType, p.Category, SUM(oi.Quantity) AS TotalUnitsSold, SUM(o.LocalAmount * o.ExchangeRate) AS RevenueUSD, AVG(o.LocalAmount * o.ExchangeRate) AS AvgPriceUSD, MIN(o.LocalAmount * o.ExchangeRate) AS MinPriceUSD, MAX(o.LocalAmount * o.ExchangeRate) AS MaxPriceUSD, COUNT(*) AS TransactionCount FROM dbo.GlobalSalesOrders o JOIN dbo.OrderItems oi ON o.OrderId oi.OrderId JOIN dbo.GlobalProducts p ON oi.ProductId p.ProductId WHERE o.Status Completed GROUP BY GROUPING SETS ( (p.MaterialType, p.Category), -- 材质 x 品类 (p.MaterialType), -- 材质总计 (p.Category), -- 品类总计 () -- 全局总计 ) ORDER BY GROUPING(p.MaterialType), p.MaterialType, GROUPING(p.Category), p.Category; -- -- 4. 客户生命周期价值 (CLV) 分析 -- WITH CustomerMetrics AS ( SELECT c.CustomerId, c.MembershipTier, c.RegisterDate, MAX(o.OrderDateUTC) AS LastPurchaseDate, SUM(o.LocalAmount * o.ExchangeRate) AS LifetimeValueUSD, COUNT(o.OrderId) AS TotalOrders FROM dbo.GlobalCustomers c LEFT JOIN dbo.GlobalSalesOrders o ON c.CustomerId o.CustomerId AND o.Status Completed GROUP BY c.CustomerId, c.MembershipTier, c.RegisterDate ) SELECT MembershipTier, COUNT(CustomerId) AS CustomerCount, SUM(LifetimeValueUSD) AS TotalRevenueUSD, AVG(LifetimeValueUSD) AS AvgCLVUSD, AVG(TotalOrders) AS AvgOrdersPerCustomer, -- 识别流失最后购买日期距今超过90天 SUM(CASE WHEN DATEDIFF(day, LastPurchaseDate, GETUTCDATE()) 90 THEN 1 ELSE 0 END) AS AtRiskCustomers FROM CustomerMetrics GROUP BY GROUPING SETS ( (MembershipTier), () ); -- -- 5. 人力成本效率分析 (HR) -- -- 假设我们有一个汇率查找表这里为了简化直接在查询中映射常见汇率 -- 实际生产中建议使用独立的 ExchangeRates 表 DECLARE ExchangeRateMap TABLE (Currency CHAR(3), RateToUSD DECIMAL(10,6)); INSERT INTO ExchangeRateMap VALUES (USD, 1.0), (EUR, 1.08), (JPY, 0.0067), (CNY, 0.14), (GBP, 1.27); SELECT s.Region, e.Department, COUNT(e.EmployeeId) AS Headcount, SUM(e.SalaryLocal * erm.RateToUSD) AS TotalCostUSD, AVG(e.SalaryLocal * erm.RateToUSD) AS AvgCostPerEmployeeUSD, -- 计算人力成本效率每美元薪资带来的销售额 (需结合销售数据) -- 这里仅展示成本结构若需效率需关联 Sales 数据 CASE WHEN SUM(e.SalaryLocal * erm.RateToUSD) 0 THEN 1000000 / NULLIF(SUM(e.SalaryLocal * erm.RateToUSD), 0) -- 伪指标每百万美元薪资对应多少员工 ELSE 0 END AS CostEfficiencyProxy FROM dbo.GlobalEmployees e JOIN dbo.GlobalStores s ON e.CountryCode s.CountryCode LEFT JOIN ExchangeRateMap erm ON e.SalaryCurrency erm.Currency GROUP BY GROUPING SETS ( (s.Region, e.Department), -- 区域 x 部门 (s.Region), -- 区域总计 (e.Department), -- 部门总计 () -- 全球总计 ) ORDER BY GROUPING(s.Region), s.Region, GROUPING(e.Department), e.Department; -- -- 6. 月度销售增长率分析 -- WITH MonthlySales AS ( SELECT s.StoreName, FORMAT(o.OrderDateUTC, yyyy-MM) AS SaleMonth, SUM(o.LocalAmount * o.ExchangeRate) AS SalesUSD FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId WHERE o.Status Completed AND YEAR(o.OrderDateUTC) IN (2025, 2026) -- 查看近两年数据 GROUP BY s.StoreName, FORMAT(o.OrderDateUTC, yyyy-MM) ), WithGrowth AS ( SELECT StoreName, SaleMonth, SalesUSD, LAG(SalesUSD) OVER (PARTITION BY StoreName ORDER BY SaleMonth) AS PrevMonthSales, LAG(SalesUSD, 12) OVER (PARTITION BY StoreName ORDER BY SaleMonth) AS SameMonthLastYearSales FROM MonthlySales ) SELECT StoreName, SaleMonth, SalesUSD, PrevMonthSales, SameMonthLastYearSales, -- 环比增长率 CASE WHEN PrevMonthSales IS NOT NULL AND PrevMonthSales 0 THEN (SalesUSD - PrevMonthSales) / PrevMonthSales * 100 ELSE NULL END AS MoMGrowthPct, -- 同比增长率 CASE WHEN SameMonthLastYearSales IS NOT NULL AND SameMonthLastYearSales 0 THEN (SalesUSD - SameMonthLastYearSales) / SameMonthLastYearSales * 100 ELSE NULL END AS YoYGrowthPct FROM WithGrowth ORDER BY StoreName, SaleMonth; sql: SQL Aggregate Functions and Grouping Sets using sql server 2025
-- -- 1. 环境准备创建数据库并切换上下文 -- IF DB_ID(JewelryGlobalDB) IS NULL BEGIN CREATE DATABASE JewelryGlobalDB; END GO USE JewelryGlobalDB; GO -- -- 2. 表结构定义 (DDL) -- -- 2.1 全球门店表 (Stores) CREATE TABLE dbo.GlobalStores ( StoreId INT IDENTITY(1,1) PRIMARY KEY, StoreName NVARCHAR(200) NOT NULL, CountryCode CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 Region NVARCHAR(50) NOT NULL, -- e.g., EMEA, APAC, Americas CurrencyCode CHAR(3) NOT NULL, -- 本地结算货币 TimeZone NVARCHAR(50) NOT NULL, -- IANA Time Zone Name OpenDate DATE NOT NULL ); -- 2.2 全球产品目录 (Products) CREATE TABLE dbo.GlobalProducts ( ProductId INT IDENTITY(1,1) PRIMARY KEY, BrandLine NVARCHAR(50) NOT NULL, -- e.g., Tiffany Co., Cartier Category NVARCHAR(50) NOT NULL, -- e.g., Diamond Rings, Gold Chains MaterialType NVARCHAR(50) NOT NULL, -- e.g., Platinum 950, 18K Rose Gold BasePriceUSD DECIMAL(18,2) NOT NULL, -- 统一基准定价 (USD) WeightGrams DECIMAL(10,2) NULL -- 重量用于黄金/铂金类计算 ); -- 2.3 全球销售订单 (SalesOrders) CREATE TABLE dbo.GlobalSalesOrders ( OrderId INT IDENTITY(1,1) PRIMARY KEY, StoreId INT NOT NULL, OrderDateUTC DATETIMEOFFSET(0) NOT NULL, -- 存储 UTC0 时间 CustomerId INT NULL, -- 允许匿名购买 LocalAmount DECIMAL(18,2) NOT NULL, -- 交易发生时的本地金额 LocalCurrency CHAR(3) NOT NULL, -- 交易货币 ExchangeRate DECIMAL(10,6) NOT NULL, -- 当日汇率 (1 USD ? Local) Status NVARCHAR(20) NOT NULL -- Completed, Cancelled, Returned ); -- 2.4 订单明细 (OrderItems) - 用于精确到SKU的销售分析 CREATE TABLE dbo.OrderItems ( OrderItemId INT IDENTITY(1,1) PRIMARY KEY, OrderId INT NOT NULL, ProductId INT NOT NULL, Quantity INT NOT NULL, UnitPriceLocal DECIMAL(18,2) NOT NULL, -- 单品本地单价 LineAmountLocal DECIMAL(18,2) NOT NULL -- 行总额 ); -- 2.5 全球客户 (Customers) CREATE TABLE dbo.GlobalCustomers ( CustomerId INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(100), LastName NVARCHAR(100), MembershipTier NVARCHAR(20) NOT NULL, -- Silver, Gold, Platinum, Black HomeCountry CHAR(2) NULL, Email NVARCHAR(200) NULL, RegisterDate DATETIMEOFFSET(0) NOT NULL ); -- 2.6 全球员工 (Employees) CREATE TABLE dbo.GlobalEmployees ( EmployeeId INT IDENTITY(1,1) PRIMARY KEY, FullName NVARCHAR(100) NOT NULL, Department NVARCHAR(50) NOT NULL, -- Retail, Manufacturing, Logistics, Corporate CountryCode CHAR(2) NOT NULL, Position NVARCHAR(50) NOT NULL, SalaryLocal DECIMAL(18,2) NOT NULL, SalaryCurrency CHAR(3) NOT NULL ); -- -- 3. 外键约束 (FK) -- ALTER TABLE dbo.GlobalSalesOrders ADD CONSTRAINT FK_Orders_Store FOREIGN KEY (StoreId) REFERENCES dbo.GlobalStores(StoreId); ALTER TABLE dbo.GlobalSalesOrders ADD CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerId) REFERENCES dbo.GlobalCustomers(CustomerId); ALTER TABLE dbo.OrderItems ADD CONSTRAINT FK_Items_Order FOREIGN KEY (OrderId) REFERENCES dbo.GlobalSalesOrders(OrderId); ALTER TABLE dbo.OrderItems ADD CONSTRAINT FK_Items_Product FOREIGN KEY (ProductId) REFERENCES dbo.GlobalProducts(ProductId); -- -- 4. 示例数据插入 (DML) -- -- 4.1 插入门店数据 (覆盖主要市场) INSERT INTO dbo.GlobalStores (StoreName, CountryCode, Region, CurrencyCode, TimeZone, OpenDate) VALUES (New York Fifth Avenue, US, Americas, USD, America/New_York, 2010-05-15), (Paris Place Vendôme, FR, EMEA, EUR, Europe/Paris, 2012-09-01), (Tokyo Ginza, JP, APAC, JPY, Asia/Tokyo, 2015-03-20), (Shanghai Nanjing Road, CN, APAC, CNY, Asia/Shanghai, 2018-11-11), (London Bond Street, GB, EMEA, GBP, Europe/London, 2011-06-01); -- 4.2 插入产品数据 INSERT INTO dbo.GlobalProducts (BrandLine, Category, MaterialType, BasePriceUSD, WeightGrams) VALUES (Tiffany Co., Diamond Rings, Platinum 950, 12000.00, NULL), (Tiffany Co., Necklaces, 18K Yellow Gold, 3500.00, 15.50), (Cartier, Bracelets, 18K Rose Gold, 8500.00, 25.00), (Chopard, Watches, Steel Diamonds, 25000.00, NULL), (Bulgari, Rings, 18K White Gold, 4200.00, 8.00); -- 4.3 插入客户数据 INSERT INTO dbo.GlobalCustomers (FirstName, LastName, MembershipTier, HomeCountry, Email, RegisterDate) VALUES (John, Smith, Gold, US, john.smithexample.com, 2023-01-10 10:00:00 00:00), (Marie, Dubois, Platinum, FR, marie.duboisexample.com, 2022-05-20 14:30:00 00:00), (Kenji, Tanaka, Silver, JP, kenji.tanakaexample.com, 2024-02-14 09:15:00 00:00), (Wei, Zhang, Black, CN, wei.zhangexample.com, 2021-11-11 20:00:00 00:00), (Sarah, Connor, Gold, GB, sarah.connorexample.com, 2023-06-01 11:00:00 00:00); -- 4.4 插入员工数据 INSERT INTO dbo.GlobalEmployees (FullName, Department, CountryCode, Position, SalaryLocal, SalaryCurrency) VALUES (Alice Johnson, Retail, US, Store Manager, 120000.00, USD), (Pierre Martin, Retail, FR, Sales Associate, 45000.00, EUR), (Yuki Sato, Manufacturing, JP, Gem Setter, 6000000.00, JPY), (Li Wei, Retail, CN, Store Manager, 300000.00, CNY), (James Brown, Logistics, GB, Warehouse Lead, 55000.00, GBP); -- 4.5 插入销售订单 (注意汇率模拟时间为2026年) -- 假设汇率: 1 USD 0.92 EUR, 1 USD 148 JPY, 1 USD 7.2 CNY, 1 USD 1.27 GBP INSERT INTO dbo.GlobalSalesOrders (StoreId, OrderDateUTC, CustomerId, LocalAmount, LocalCurrency, ExchangeRate, Status) VALUES (1, 2026-09-15 14:30:00 -04:00, 1, 12000.00, USD, 1.000000, Completed), -- US Store, USD (2, 2026-09-16 10:00:00 02:00, 2, 11040.00, EUR, 0.920000, Completed), -- FR Store, EUR (11040 * 0.92 10156.8 USD approx) (3, 2026-09-17 15:00:00 09:00, 3, 1776000.00, JPY, 148.000000, Completed), -- JP Store, JPY (4, 2026-09-18 11:00:00 08:00, 4, 86400.00, CNY, 7.200000, Completed), -- CN Store, CNY (5, 2026-09-19 16:00:00 01:00, 5, 15200.00, GBP, 1.270000, Completed), -- UK Store, GBP (1, 2026-09-20 09:00:00 -04:00, NULL, 3500.00, USD, 1.000000, Completed); -- Walk-in customer -- 4.6 插入订单明细 INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPriceLocal, LineAmountLocal) VALUES (1, 1, 1, 12000.00, 12000.00), (2, 3, 1, 11040.00, 11040.00), (3, 2, 1, 1776000.00, 1776000.00), (4, 4, 1, 86400.00, 86400.00), (5, 1, 1, 15200.00, 15200.00), (6, 2, 1, 3500.00, 3500.00); -- -- 5. 性能优化索引 -- -- 加速按区域和日期范围查询 CREATE INDEX IX_Orders_DateRegion ON dbo.GlobalSalesOrders (OrderDateUTC, StoreId); -- 加速按客户查询历史订单 CREATE INDEX IX_Orders_Customer ON dbo.GlobalSalesOrders (CustomerId, OrderDateUTC); -- 加速按产品类别查询 CREATE INDEX IX_Items_Product ON dbo.OrderItems (ProductId); -- -- 6. 验证数据 (可选运行) -- SELECT s.StoreName, o.LocalCurrency, COUNT(o.OrderId) AS OrderCount FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId GROUP BY s.StoreName, o.LocalCurrency ORDER BY OrderCount DESC; -- 快速验证计算各区域的总销售额 (USD) SELECT s.Region, SUM(o.LocalAmount * o.ExchangeRate) AS TotalSalesUSD FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId WHERE o.Status Completed GROUP BY s.Region ORDER BY TotalSalesUSD DESC; -- -- 1. 全球区域 x 品牌销售汇总 -- SELECT COALESCE(s.Region, GLOBAL TOTAL) AS Region, COALESCE(p.BrandLine, ALL BRANDS) AS BrandLine, -- 聚合指标 COUNT(DISTINCT o.OrderId) AS GlobalOrderCount, SUM(o.LocalAmount * o.ExchangeRate) AS SalesUSD, AVG(o.LocalAmount * o.ExchangeRate) AS AvgTicketUSD, -- 统计活跃国家数 COUNT(DISTINCT s.CountryCode) AS ActiveCountries FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId JOIN dbo.OrderItems oi ON o.OrderId oi.OrderId JOIN dbo.GlobalProducts p ON oi.ProductId p.ProductId WHERE o.Status Completed AND o.OrderDateUTC 2026-09-01 -- 限定最近一个月 AND o.OrderDateUTC 2026-10-01 GROUP BY GROUPING SETS ( (s.Region, p.BrandLine), -- 明细区域 x 品牌 (s.Region), -- 小计区域总计 (p.BrandLine), -- 小计品牌总计 () -- 总计全球总计 ) ORDER BY GROUPING(s.Region), -- 先排有值的区域 s.Region, GROUPING(p.BrandLine), -- 再排有值的品牌 p.BrandLine; -- -- 2. 高净值客户 (HNI) 行为分析 (修正版) -- WITH CustomerMetrics AS ( -- 第一步计算每个客户的个人指标 SELECT c.CustomerId, c.MembershipTier, c.HomeCountry, SUM(o.LocalAmount * o.ExchangeRate) AS TotalSpendUSD, COUNT(o.OrderId) AS OrderCount, MAX(o.OrderDateUTC) AS LastPurchaseDate FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalCustomers c ON c.CustomerId o.CustomerId WHERE c.MembershipTier IN (Platinum, Black) AND o.Status Completed GROUP BY c.CustomerId, c.MembershipTier, c.HomeCountry ), CustomerBehavior AS ( -- 第二步衍生指标计算 SELECT CustomerId, MembershipTier, HomeCountry, TotalSpendUSD, OrderCount, LastPurchaseDate, DATEDIFF(day, LastPurchaseDate, GETUTCDATE()) AS DaysSinceLastPurchase, CASE WHEN DATEDIFF(day, LastPurchaseDate, GETUTCDATE()) 90 THEN At Risk ELSE Active END AS RetentionStatus FROM CustomerMetrics ) -- 第三步最终汇总展示 SELECT COALESCE(MembershipTier, GLOBAL TOTAL) AS Tier, COALESCE(HomeCountry, ALL COUNTRIES) AS Country, COUNT(CustomerId) AS UniqueHighNetWorthCustomers, SUM(TotalSpendUSD) AS TotalContributionUSD, ROUND(AVG(TotalSpendUSD), 2) AS AvgSpendPerCustomerUSD, ROUND(AVG(OrderCount), 2) AS AvgOrdersPerCustomer, SUM(CASE WHEN RetentionStatus At Risk THEN 1 ELSE 0 END) AS AtRiskCount, SUM(CASE WHEN RetentionStatus Active THEN 1 ELSE 0 END) AS ActiveCount, -- 生成一个整数用于排序层级越高数字越大 GROUPING_ID(MembershipTier, HomeCountry) AS GroupingLevel FROM CustomerBehavior GROUP BY GROUPING SETS ( (MembershipTier, HomeCountry), -- 明细 (MembershipTier), -- 等级小计 (HomeCountry), -- 国家小计 () -- 总计 ) HAVING SUM(TotalSpendUSD) 1000 -- 过滤低贡献数据 ORDER BY GroupingLevel DESC, -- 先排总计再排小计最后排明细 MembershipTier ASC, -- 同级内按会员等级排序 Country ASC; -- 最后按国家排序 -- -- 3. 材质与品类销售分析 (ERP) -- SELECT p.MaterialType, p.Category, SUM(oi.Quantity) AS TotalUnitsSold, SUM(o.LocalAmount * o.ExchangeRate) AS RevenueUSD, AVG(o.LocalAmount * o.ExchangeRate) AS AvgPriceUSD, MIN(o.LocalAmount * o.ExchangeRate) AS MinPriceUSD, MAX(o.LocalAmount * o.ExchangeRate) AS MaxPriceUSD, COUNT(*) AS TransactionCount FROM dbo.GlobalSalesOrders o JOIN dbo.OrderItems oi ON o.OrderId oi.OrderId JOIN dbo.GlobalProducts p ON oi.ProductId p.ProductId WHERE o.Status Completed GROUP BY GROUPING SETS ( (p.MaterialType, p.Category), -- 材质 x 品类 (p.MaterialType), -- 材质总计 (p.Category), -- 品类总计 () -- 全局总计 ) ORDER BY GROUPING(p.MaterialType), p.MaterialType, GROUPING(p.Category), p.Category; -- -- 4. 客户生命周期价值 (CLV) 分析 -- WITH CustomerMetrics AS ( SELECT c.CustomerId, c.MembershipTier, c.RegisterDate, MAX(o.OrderDateUTC) AS LastPurchaseDate, SUM(o.LocalAmount * o.ExchangeRate) AS LifetimeValueUSD, COUNT(o.OrderId) AS TotalOrders FROM dbo.GlobalCustomers c LEFT JOIN dbo.GlobalSalesOrders o ON c.CustomerId o.CustomerId AND o.Status Completed GROUP BY c.CustomerId, c.MembershipTier, c.RegisterDate ) SELECT MembershipTier, COUNT(CustomerId) AS CustomerCount, SUM(LifetimeValueUSD) AS TotalRevenueUSD, AVG(LifetimeValueUSD) AS AvgCLVUSD, AVG(TotalOrders) AS AvgOrdersPerCustomer, -- 识别流失最后购买日期距今超过90天 SUM(CASE WHEN DATEDIFF(day, LastPurchaseDate, GETUTCDATE()) 90 THEN 1 ELSE 0 END) AS AtRiskCustomers FROM CustomerMetrics GROUP BY GROUPING SETS ( (MembershipTier), () ); -- -- 5. 人力成本效率分析 (HR) -- -- 假设我们有一个汇率查找表这里为了简化直接在查询中映射常见汇率 -- 实际生产中建议使用独立的 ExchangeRates 表 DECLARE ExchangeRateMap TABLE (Currency CHAR(3), RateToUSD DECIMAL(10,6)); INSERT INTO ExchangeRateMap VALUES (USD, 1.0), (EUR, 1.08), (JPY, 0.0067), (CNY, 0.14), (GBP, 1.27); SELECT s.Region, e.Department, COUNT(e.EmployeeId) AS Headcount, SUM(e.SalaryLocal * erm.RateToUSD) AS TotalCostUSD, AVG(e.SalaryLocal * erm.RateToUSD) AS AvgCostPerEmployeeUSD, -- 计算人力成本效率每美元薪资带来的销售额 (需结合销售数据) -- 这里仅展示成本结构若需效率需关联 Sales 数据 CASE WHEN SUM(e.SalaryLocal * erm.RateToUSD) 0 THEN 1000000 / NULLIF(SUM(e.SalaryLocal * erm.RateToUSD), 0) -- 伪指标每百万美元薪资对应多少员工 ELSE 0 END AS CostEfficiencyProxy FROM dbo.GlobalEmployees e JOIN dbo.GlobalStores s ON e.CountryCode s.CountryCode LEFT JOIN ExchangeRateMap erm ON e.SalaryCurrency erm.Currency GROUP BY GROUPING SETS ( (s.Region, e.Department), -- 区域 x 部门 (s.Region), -- 区域总计 (e.Department), -- 部门总计 () -- 全球总计 ) ORDER BY GROUPING(s.Region), s.Region, GROUPING(e.Department), e.Department; -- -- 6. 月度销售增长率分析 -- WITH MonthlySales AS ( SELECT s.StoreName, FORMAT(o.OrderDateUTC, yyyy-MM) AS SaleMonth, SUM(o.LocalAmount * o.ExchangeRate) AS SalesUSD FROM dbo.GlobalSalesOrders o JOIN dbo.GlobalStores s ON s.StoreId o.StoreId WHERE o.Status Completed AND YEAR(o.OrderDateUTC) IN (2025, 2026) -- 查看近两年数据 GROUP BY s.StoreName, FORMAT(o.OrderDateUTC, yyyy-MM) ), WithGrowth AS ( SELECT StoreName, SaleMonth, SalesUSD, LAG(SalesUSD) OVER (PARTITION BY StoreName ORDER BY SaleMonth) AS PrevMonthSales, LAG(SalesUSD, 12) OVER (PARTITION BY StoreName ORDER BY SaleMonth) AS SameMonthLastYearSales FROM MonthlySales ) SELECT StoreName, SaleMonth, SalesUSD, PrevMonthSales, SameMonthLastYearSales, -- 环比增长率 CASE WHEN PrevMonthSales IS NOT NULL AND PrevMonthSales 0 THEN (SalesUSD - PrevMonthSales) / PrevMonthSales * 100 ELSE NULL END AS MoMGrowthPct, -- 同比增长率 CASE WHEN SameMonthLastYearSales IS NOT NULL AND SameMonthLastYearSales 0 THEN (SalesUSD - SameMonthLastYearSales) / SameMonthLastYearSales * 100 ELSE NULL END AS YoYGrowthPct FROM WithGrowth ORDER BY StoreName, SaleMonth;