解锁SQL无限可能 | 如何超越基础，成为数据分析高手？

会飞的一十六 2024-10-28

295

点击上方【蓝色】字体关注我们

在前面的系列文章中，我们讲了SQL很多进阶的技巧，本文让我们更进一步探索一些SQL高级特性，这些特性将帮助你成为真正的数据分析高手，更好地玩转SQL。

SQL实践及技巧看这里：

SQL很简单，可你却总是写不好？每天一点点，收获不止一点点。

01 窗口函数：数据分析神器

窗口函数是 SQL 中最强大的特性之一，它允许你在保持行粒度的同时执行复杂的计算。我们可以利用窗口函数构建辅助变量参与到实际计算中，让SQL分析实际问题更进一步。

实例：计算移动平均

假设我们要计算产品销售的 7 天移动平均：

SELECT 
    date,
    product,
    sales,
    AVG(sales) OVER (
        PARTITION BY product 
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM 
    daily_sales;

这个查询为每个产品计算了 7 天的移动平均，而不需要复杂的自连接或子查询。

02 公共表达式（CTE）

CTE 可以让你将复杂的查询分解成更小、更易管理的部分，让SQL可读性及可维护性增强，是提升SQL代码管理的利器。

实例：分析销售增长

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', date) AS month,
        SUM(sales) AS total_sales
    FROM 
        daily_sales
    GROUP BY 
        DATE_TRUNC('month', date)
),
sales_growth AS (
    SELECT 
        month,
        total_sales,
        LAG(total_sales) OVER (ORDER BY month) AS prev_month_sales
    FROM 
        monthly_sales
)
SELECT 
    month,
    total_sales,
    (total_sales - prev_month_sales)  prev_month_sales * 100 AS growth_rate
FROM 
    sales_growth
WHERE 
    prev_month_sales IS NOT NULL;

这个查询使用 CTE 将复杂的分析过程分解成易于理解的步骤。

03 递归查询

递归查询允许你处理树状或层级结构的数据，可以解决一些更复杂的问题，如组织结构或产品类别。

实例：展开员工层级

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

这个查询可以展示完整的员工层级结构，无论层级有多深。

最短路径问题

示例表，dist 存储了目的地到出发地的距离，我们要计算出从 a 地出发到其它地点的最短距离。

sp      ep      distance  
------  ------  ----------
a       b                5
a       c                1
b       c                2
b       d                1
c       d                4
c       e                8
d       e                3
d       f                6

WITH RECURSIVE t (sp, ep, distance, path) AS 
(SELECT 
  *,
  CAST(CONCAT(a.sp, ' -> ', a.ep) AS CHAR(100)) AS path 
FROM
  dist a 
WHERE sp = 'a' 
UNION ALL 
SELECT 
  t.sp,
  b.ep,
  t.distance + b.distance,
  CAST(
    CONCAT(t.path, ' -> ', b.ep) AS CHAR(100)
  ) AS path 
FROM
  t 
  INNER JOIN dist b 
    ON b.sp = t.ep 
    AND INSTR(t.path, b.ep) <= 0),
t1 AS 
(SELECT 
  *,
  row_number () over (
    PARTITION BY sp,
    ep 
ORDER BY distance
) AS rn 
FROM
  t) 
  
SELECT 
  sp,
  ep,
  path,
  distance 
FROM
  t1 
WHERE rn = 1

sp      ep      path             distance  
------  ------  ---------------  ----------
a       b       a -> b                     5
a       c       a -> c                     1
a       d       a -> c -> d                5
a       e       a -> c -> d -> e           8
a       f       a -> c -> d -> f          11

04 高级聚合：统计函数

SQL 提供了许多高级的聚合函数，可以进行复杂的数据汇总及数理统计计算。

实例：计算中位数和四分位数

SELECT 
    product,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) AS first_quartile,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) AS third_quartile
FROM 
    products
GROUP BY 
    product;

这个查询计算了每种产品价格的中位数和四分位数，提供了比简单的平均值更丰富的价格分布信息。

05 全文检索：处理非结构化数据

许多现代数据库支持全文搜索，允许你高效地搜索大量文本数据，如doris数据库等。

实例：在产品描述中搜索关键词

SELECT 
    product_name,
    description
FROM 
    products
WHERE 
    to_tsvector('english', description) @@ to_tsquery('english', 'durable & waterproof');

这个查询使用全文搜索功能来查找描述中包含"durable"和"waterproof"这两个词的产品。

06 特征工程构建

（1）数据库中基本内置的函数及窗口函数可以帮助我们构建一些数据特征。

例如：块熵的计算

数据特征工程：如何计算块熵？| 基于SQL实现

数据特征工程：一种时序数据的波峰个数检测算法 | 基于SQL实现

（2）利用UDF函数构建特征工程

我们可以使用Flask创建一个简单的预测服务，然后通过Doris的自定义函数（UDF）来调用这个服务：

首先，创建一个Flask应用来提供预测服务：

from flask import Flask, request, jsonify
import joblib


app = Flask(__name__)
model = joblib.load('recommendation_model.pkl')


@app.route('/predict', methods=['POST'])
def predict():
    data = request.json
    prediction = model.predict(data['user_id'], data['item_id'])
    return jsonify({'prediction': float(prediction)})


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)

然后，在Doris中创建一个UDF来调用这个预测服务：

CREATE FUNCTION predict_rating(user_id INT, item_id INT) RETURNS DOUBLE PROPERTIES (
    "type" = "JAVA_UDF",
    "file" = "file:///path/to/predict_udf.jar",
    "class" = "com.example.doris.udf.PredictRating",
    "method" = "evaluate"
);

在Java UDF中实现对预测服务的调用：

public class PredictRating extends ScalarFunction {
    public Double evaluate(Integer userId, Integer itemId) {
        // 调用预测服务的逻辑
        // 返回预测结果
    }
}

4.现在我们可以在Doris中直接使用这个UDF进行预测

SELECT 
    user_id, 
    item_id, 
    predict_rating(user_id, item_id) AS predicted_rating
FROM user_item_interactions
WHERE predict_rating(user_id, item_id) > 4.0;

这个查询可以找出模型预测评分高于4.0的所有用户-物品对，可以用于推荐系统。

随着AI技术的普及，现在比较流行的数据库都会算法模型相结合，实现预测性分析。虽然数据库本身不直接支持机器学习，但我们可以通过以下方式实现：

使用数据库进行数据预处理和特征工程
将处理后的数据导出到支持机器学习的平台（如Spark MLlib）
训练模型并生成预测结果
将预测结果导回数据库进行存储和进一步分析

07 小结

这些高级 SQL 技巧只是冰山一角。随着你不断深入学习和实践，你会发现 SQL 的强大远超想象。它不仅是一种查询语言，更是一种数据处理和分析的利器。掌握这些高级特性将使你能够让你处理复杂需求游刃有余，让你SQL数据处理能力更上一个台阶。记住，成为 SQL 专家的关键在于不断实践和探索。每解决一个复杂的数据问题，你就离成为真正的数据分析高手更近一步。继续学习，勇于尝试，你会发现 SQL 世界的精彩远超你的想象！

看行不是行，看列不是列，行即是列，列即是行，无中生有，让你的SQL飞起来！！！