关于Kaggle上的SQL Mutual Fund Performance代码

上文提到，Kaggle 是一个数据建模和数据分析竞赛平台，US Funds dataset from Yahoo Finance是Kaggle上的一个数据集。

Kaggle 上不仅提供了数据，也提供了很多数据科学工作者的代码。在Kaggle上，US Funds dataset from Yahoo Finance数据集对应的分析代码有19个：

其中“SQL Mutual Fund Performance”立即引起了笔者的关注。

代码地址：https://www.kaggle.com/code/sophianguyn/sql-mutual-fund-performance

先看一下该代码的总结：

1. 问题陈述-本项目的目标是使用 SQL 分析共同基金数据，以确定关键业绩趋势、风险与回报矩阵以及费用预测。随着投资机会的增加，了解这些方面对于投资者做出明智的决策至关重要。此分析旨在对基金进行分类、比较业绩并了解风险回报权衡，为投资者和基金经理提供宝贵的见解。
2. 结论-分析揭示了共同基金业绩、风险评级和回报评级的重要趋势。通过根据业绩和风险回报矩阵对基金进行分类，我们确定了高绩效和低绩效基金： 高绩效基金：持续表现出高评级和有利风险回报率的基金。 低绩效基金：评级低且风险回报率较差的基金。 此外，不同时间范围（3、5 和 10 年）的费用预测提供了对不同基金相关成本的全面了解。例如，“13D Activist Fund”和“361 Funds”等基金多年来的费用趋势高于其他基金。这种整体视角有助于做出更好的投资决策，因为它可以突出各种共同基金的业绩潜力和成本影响。
3. 技术-使用 SQL 对数据进行分类和聚合。 基金业绩、风险评级和回报评级的比较分析。 3 年、5 年和 10 年期间的费用预测分析。 可视化关键发现以增强可解释性。
4. 如果我有更多时间，我可能会做 集成更复杂的统计模型，根据历史数据预测未来表现。 对市场条件对共同基金业绩的影响进行更深入的分析。 实施高级可视化工具以创建更具交互性和更详细的数据可视化表示。

问题引入：这些SQL代码可以跑在云器Lakehouse上么？

云器Lakehouse支持SQL和Python等语言进行数据分析，这就激发了笔者对其中“SQL Mutual Fund Performance”SQL代码的好奇心：

❓ 这些SQL代码可以跑在云器Lakehouse上，以进一步分析上文导入到Lakehouse的数据么？

SQL Mutual Fund Performance包括了如下查询：

查询 1：四年内（2018-2021）最高和最低平均评级 查询 2：每只基金的平均同比评级 查询 3：关键评级 KPI 同比比较表 查询 4：根据基金表现和风险回报矩阵对基金进行分类 查询 5：表现和风险与回报矩阵分析 查询 6：业务审查的风险与回报矩阵分析 查询 7：最高营业额分析 查询 8：费用项目趋势

SQL特点和复杂度分析

凡事先谋而后动，在决定正式代码迁移之前，先仔细阅读一下“SQL Mutual Fund Performance”代码，分析其特点和复杂度。快速看下查询 2（每只基金的平均同比评级）的SQL代码（Pandas SQL语法）：

特点

1. 使用公共表表达式（CTE）：

   1. 通过WITH子句定义了一个名为expense的CTE，用于计算每个基金家族的费用趋势。这使得查询结构更加清晰和模块化。

2. 条件聚合：

   1. 使用SUM和CASE WHEN语句来计算不同时间段内的费用趋势。这种方法允许在单个查询中进行复杂的条件聚合。

3. 联合查询（UNION ALL）：

   1. 使用UNION ALL将多个子查询的结果合并在一起，每个子查询代表不同的时间段（3年、5年、10年）的费用趋势。

4. 分组和聚合：

   1. 在最终的查询中，使用GROUP BY和MAX函数对不同基金家族的费用趋势进行分组和聚合。

复杂度

1. 查询嵌套：

   1. 查询包含多个嵌套的子查询和CTE。

2. 条件逻辑：

   1. 使用了多个CASE WHEN语句来处理不同的条件逻辑。

3. 性能考虑：

   1. 由于查询涉及多个聚合操作和条件计算，对数据平台对大数据集的计算性能要求高。

可以看到，这些SQL查询通过使用CTE、条件聚合和联合查询来处理复杂的数据分析任务，因此增加了查询的复杂度和执行时的性能挑战。

迁移评估

通过以上分析可以看到，为了对共同基金进行专业分析，“SQL Mutual Fund Performance”代码确实具有复杂性。而云器Lakehouse的SQL引擎对Spark SQL、Pandas SQL具有高度兼容性，肉眼可见的是一个非常微小的兼容性改动：

💡 将strftime('%Y', inception_date) 改为year(inception_date)

经过上述分析，大幅降低了SQL作业迁移难度的心理顾虑，开搞！

SQL作业迁移步骤计划

云器Lakehouse内置有SQL IDE开发工具，包括代码智能提示、版本管理、测试和发布等，愉快的迁移过程就此开始：

在上文数据加载的基础上规划本次迁移的四个步骤：

1. 用动态表（Dynamic table）来存放计算结果，考虑到底表变化和结果表都是统计结果，非常适合用动态表来简化数据加工逻辑。
2. 数据标准：对数据进行详细注释，方便团队理解和大模型理解数据结构。
3. 刷新动态表：不同于传统的大数据INSERT INTO/OVERWRITE的结果刷新方式，云器Lakehouse的动态表采用增量计算技术，通过REFESHESH 动态表的方式只对变化数据进行计算，从而大幅提升了数据刷新速度并降级计算成本，从而提高性价比，达到降本增效的目的。
4. 查看结果，通过SELECT语句查看计算结果。

实施SQL作业迁移

迁移过程非常顺利，基本上是个ICP（Information Copy&Paste）的过程。每一个查询的迁移过程如下：

1.从“SQL Mutual Fund Performance”Copy代码。

2.粘贴到云器Lakehouse的SQL IDE。

3.先跑通SELECT语句，跑通后检查结果正确。

a.语法修改：使用IDE的查找替换功能，将strftime('%Y', inception_date) 改为year(inception_date)

b.检查结果：

4.根据SELECT语句创建动态表。

5.迁移后完整的代码如下：

❤️完整代码都放这里了，数据在上文也都有了，你会动手玩起来么？

数据标注

这里是数据工程师的美德了！小伙伴们需要更好的理解数据，大模型也同样需要。

执行完上述代码，看下小伙伴们是不是就更好理解你的数据产出啦（你不是一个人在战斗！）：

刷新动态表

当第一次建好动态表或者之后在底表数据发生变化时，可以通过执行如下语句刷新动态表里的数据：

可以看到在云器Lakehouse执行上述10个动态表的结果刷新，仅用了1.6秒。而这10个刷新动作的底表有7千多万行数据，之前担心执行性能的问题，发现也不是问题嘞。

也可以周期性的调度上述代码：

通过设置每次调度间隔时长为1分钟，这样就可以将动态表的数据新鲜度保持在分钟级了，也就是说当底表数据发生变化1分钟后，结果表的结果就会自动被刷新了。

关于云器Lakehouse动态表详细解释，参考：https://www.yunqi.tech/documents/dynamic_table_summary

查看结果

一次查询10个动态表里的结果，仅用了656毫秒。

性能体验

同样的数据在Notebook里运行需要31秒，迁移到云器Lakehouse后，计算和分析的过程仅需要2.3秒，从性能体验的角度看提升了13.5倍，这对数据工作人员而言，确实能够大幅降低时间成本，提高工作效率。

总结

好的数据分析，好的AI效果，总是离不开好的数据。而数据工程又是个繁琐、耗时、需要很大耐心的工作。通过上述实验，将“SQL Mutual Fund Performance”那些复杂的SQL代码迁移到云器Lakehouse的心得总结如下：

1. 云器Lakehouse SQL语法高度兼容Pandas SQL，迁移过程基本上是个粘贴拷贝的过程，只有一个微小的函数语法改动。
2. 云器Lakehouse的SQL IDE提供了版本管理、测试、调度和发布集成环境，提高了开发效率。
3. 云器Lakehouse的动态表技术大幅提升了在大数据量下的性能体验，对比Notebook运行有13.5倍的性能提升。
4. 因为简单，所以美好。整个实验都是在云器Lakehouse上完成，这给整个实验带来了简单美的体验。相信因为简单，可以让更多数据爱好者更顺利的玩转数据。