160 lines
4.4 KiB
SQL
160 lines
4.4 KiB
SQL
-- =====================================================================
|
||
-- @SparkSqlName: PAIMONA-D-SQL-{表名}-QUERY
|
||
-- @Version: 1.0
|
||
-- @Desc: 标准 SELECT 查询模板
|
||
-- @TargetTables: 无（查询输出）
|
||
-- @SourceTables: {源表列表}
|
||
-- @TargetDatabase: Paimon
|
||
-- @SourceDatabase: Paimon
|
||
-- =====================================================================
|
||
|
||
-- ============================================================================
|
||
-- 基础查询示例
|
||
-- ============================================================================
|
||
|
||
-- 1. 单表查询
|
||
SELECT
|
||
id,
|
||
name,
|
||
amount,
|
||
created_at
|
||
FROM source_table
|
||
WHERE day_id = '${day_id}' -- 分区过滤
|
||
AND status = 'active' -- 业务过滤
|
||
ORDER BY created_at DESC
|
||
LIMIT 1000;
|
||
|
||
-- ============================================================================
|
||
-- JOIN 查询示例
|
||
-- ============================================================================
|
||
|
||
-- 2. 两表 JOIN
|
||
SELECT
|
||
a.id,
|
||
a.name,
|
||
b.category_name
|
||
FROM main_table a
|
||
JOIN dim_table b ON a.category_id = b.id
|
||
WHERE a.day_id = '${day_id}'
|
||
AND b.is_active = true;
|
||
|
||
-- 3. 多表 JOIN（带别名）
|
||
SELECT
|
||
o.order_id,
|
||
u.user_name,
|
||
p.product_name,
|
||
oi.quantity,
|
||
oi.unit_price
|
||
FROM orders o
|
||
JOIN users u ON o.user_id = u.id
|
||
JOIN order_items oi ON o.order_id = oi.order_id
|
||
JOIN products p ON oi.product_id = p.id
|
||
WHERE o.day_id = '${day_id}'
|
||
AND o.status IN ('completed', 'shipped');
|
||
|
||
-- ============================================================================
|
||
-- 聚合查询示例
|
||
-- ============================================================================
|
||
|
||
-- 4. GROUP BY 聚合
|
||
SELECT
|
||
department,
|
||
COUNT(*) AS employee_count,
|
||
SUM(salary) AS total_salary,
|
||
AVG(salary) AS avg_salary,
|
||
MAX(salary) AS max_salary,
|
||
MIN(salary) AS min_salary
|
||
FROM employees
|
||
WHERE day_id = '${day_id}'
|
||
GROUP BY department
|
||
HAVING COUNT(*) >= 5
|
||
ORDER BY total_salary DESC;
|
||
|
||
-- 5. 多字段分组 + 去重计数
|
||
SELECT
|
||
date,
|
||
region,
|
||
COUNT(*) AS order_count,
|
||
COUNT(DISTINCT user_id) AS unique_users,
|
||
SUM(amount) AS total_amount
|
||
FROM orders
|
||
WHERE day_id = '${day_id}'
|
||
GROUP BY date, region;
|
||
|
||
-- ============================================================================
|
||
-- 窗口函数示例
|
||
-- ============================================================================
|
||
|
||
-- 6. ROW_NUMBER（分组取Top N）
|
||
SELECT *
|
||
FROM (
|
||
SELECT
|
||
department,
|
||
name,
|
||
salary,
|
||
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
|
||
FROM employees
|
||
WHERE day_id = '${day_id}'
|
||
) t
|
||
WHERE rn <= 3; -- 每个部门薪资前3名
|
||
|
||
-- 7. 累计聚合
|
||
SELECT
|
||
date,
|
||
amount,
|
||
SUM(amount) OVER (ORDER BY date) AS cumulative_amount,
|
||
AVG(amount) OVER (
|
||
ORDER BY date
|
||
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
|
||
) AS moving_avg_7d
|
||
FROM daily_sales
|
||
WHERE day_id = '${day_id}';
|
||
|
||
-- 8. LAG/LEAD（环比计算）
|
||
SELECT
|
||
date,
|
||
amount,
|
||
LAG(amount, 1) OVER (ORDER BY date) AS prev_amount,
|
||
amount - LAG(amount, 1) OVER (ORDER BY date) AS daily_change,
|
||
ROUND((amount - LAG(amount, 1) OVER (ORDER BY date))
|
||
/ LAG(amount, 1) OVER (ORDER BY date) * 100, 2) AS growth_rate_pct
|
||
FROM daily_sales
|
||
WHERE day_id = '${day_id}';
|
||
|
||
-- ============================================================================
|
||
-- 子查询示例
|
||
-- ============================================================================
|
||
|
||
-- 9. IN 子查询
|
||
SELECT *
|
||
FROM orders
|
||
WHERE user_id IN (
|
||
SELECT id FROM users WHERE vip_level >= 3
|
||
)
|
||
AND day_id = '${day_id}';
|
||
|
||
-- 10. EXISTS 子查询
|
||
SELECT *
|
||
FROM products p
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM inventory i
|
||
WHERE i.product_id = p.id
|
||
AND i.quantity > 0
|
||
)
|
||
AND p.day_id = '${day_id}';
|
||
|
||
-- ============================================================================
|
||
-- 条件聚合示例
|
||
-- ============================================================================
|
||
|
||
-- 11. CASE WHEN + 聚合
|
||
SELECT
|
||
date,
|
||
COUNT(*) AS total_orders,
|
||
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
|
||
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count,
|
||
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
|
||
SUM(CASE WHEN amount > 1000 THEN amount ELSE 0 END) AS high_value_amount
|
||
FROM orders
|
||
WHERE day_id = '${day_id}'
|
||
GROUP BY date; |