复杂报表
生成 电影表现分析、 客户分析 、 店铺表现分析综合性的分析报告，将三部分数据合并为一个结果集。包括报告部分标识（电影/客户/店铺），项目名称（电影名/客户名/店铺ID），类别信息（电影类别/客户所在城市国家/店铺所在城市国家），各项业务指标，两类排名：类别内排名和总体排名，按报告部分和总体收入排名排序。
步骤一：配置报表配置
包含报表名称，报表描述，数据源选择，如下图所示。
步骤二：编写SQL语句
WITH rental_stats AS (
SELECT
f.film_id,
f.title,
f.rating,
c.name AS category,
COUNT(r.rental_id) AS rental_count,
SUM(p.amount) AS total_revenue,
AVG(p.amount) AS avg_revenue_per_rental,
DATEDIFF(MAX(r.rental_date), MIN(r.rental_date)) + 1 AS days_active
FROM
film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY
f.film_id,
f.title,
f.rating,
c.name
),
customer_stats AS (
SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
ci.city,
co.country,
COUNT(r.rental_id) AS total_rentals,
SUM(p.amount) AS total_spent,
MAX(r.rental_date) AS last_rental_date,
DATEDIFF(CURRENT_DATE, MAX(r.rental_date)) AS days_since_last_rental
FROM
customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name,
ci.city,
co.country
),
store_performance AS (
SELECT
s.store_id,
a.address AS store_address,
ci.city AS store_city,
co.country AS store_country,
COUNT(DISTINCT r.customer_id) AS unique_customers,
COUNT(r.rental_id) AS total_rentals,
SUM(p.amount) AS total_revenue,
COUNT(DISTINCT st.staff_id) AS staff_count
FROM
store s
JOIN address a ON s.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id
JOIN staff st ON s.store_id = st.store_id
JOIN rental r ON st.staff_id = r.staff_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY
s.store_id,
a.address,
ci.city,
co.country
)
SELECT
'电影表现' AS 报告部分,
rs.title AS 项目名称,
rs.category AS 类别,
rs.rental_count AS 租赁次数,
rs.total_revenue AS 总收入,
rs.avg_revenue_per_rental AS 平均每次租赁收入,
rs.rental_count / rs.days_active * 30 AS 预估月租赁量,
RANK() OVER (
PARTITION BY rs.category
ORDER BY
rs.total_revenue DESC
) AS 类别收入排名,
RANK() OVER (
ORDER BY
rs.total_revenue DESC
) AS 总体收入排名
FROM
rental_stats rs
WHERE
rs.rental_count > 10
UNION ALL
SELECT
'客户分析' AS 报告部分,
cs.customer_name AS 项目名称,
CONCAT(cs.city, ', ', cs.country) AS 类别,
cs.total_rentals AS 租赁次数,
cs.total_spent AS 总收入,
cs.total_spent / cs.total_rentals AS 平均每次租赁收入,
NULL AS 预估月租赁量,
RANK() OVER (
PARTITION BY cs.country
ORDER BY
cs.total_spent DESC
) AS 类别收入排名,
RANK() OVER (
ORDER BY
cs.total_spent DESC
) AS 总体收入排名
FROM
customer_stats cs
WHERE
cs.total_rentals > 5
UNION ALL
SELECT
'店铺表现' AS 报告部分,
CONCAT('店铺 #', sp.store_id) AS 项目名称,
CONCAT(sp.store_city, ', ', sp.store_country) AS 类别,
sp.total_rentals AS 租赁次数,
sp.total_revenue AS 总收入,
sp.total_revenue / sp.total_rentals AS 平均每次租赁收入,
NULL AS 预估月租赁量,
RANK() OVER (
PARTITION BY sp.store_country
ORDER BY
sp.total_revenue DESC
) AS 类别收入排名,
RANK() OVER (
ORDER BY
sp.total_revenue DESC
) AS 总体收入排名
FROM
store_performance sp
ORDER BY
报告部分,
总体收入排名
步骤三：发布
