在云器Lakehouse上玩Kaggle数据之US Funds dataset from Yahoo Finance（二）

SELECT * 
FROM (
    SELECT
        *,
        SUM(c.yr_18 + c.yr_19 + c.yr_20 + c.yr_21)  4 AS avg_rating
    FROM (
        SELECT 
            b.fund_long_name,
            MAX(b.yr_2018) AS yr_18,
            MAX(b.yr_2019) AS yr_19,
            MAX(b.yr_2020) AS yr_20,
            MAX(b.yr_2021) AS yr_21
        FROM (
            SELECT
                a.fund_long_name,
                CASE WHEN a.year = '2018' THEN a.avg_rating ELSE 0 END AS yr_2018,
                CASE WHEN a.year = '2019' THEN a.avg_rating ELSE 0 END AS yr_2019,
                CASE WHEN a.year = '2020' THEN a.avg_rating ELSE 0 END AS yr_2020,
                CASE WHEN a.year = '2021' THEN a.avg_rating ELSE 0 END AS yr_2021
            FROM (
                SELECT 
                    strftime('%Y', inception_date) AS Year,
                    fund_long_name,
                    ROUND(AVG(morningstar_overall_rating), 2) AS avg_rating
                FROM mutualfunds m
                GROUP BY fund_long_name, strftime('%Y', inception_date)
                ORDER BY fund_long_name, strftime('%Y', inception_date) DESC
            ) a
        ) b
        GROUP BY b.fund_long_name
    ) c
    GROUP BY c.fund_long_name
) d
ORDER BY d.avg_rating DESC;

特点

复杂度

CREATE DYNAMIC TABLE IF NOT EXISTS the_highest_and_lowest_average_ratings_over_years 
AS
SELECT ***

set cz.optimizer.mv.auto.unique.key.enabled = false;
-- 查询 1：四年（2018-2021 年）最高和最低平均评级 此查询的目的是计算每个基金类别的平均同比 (YoY) 评级。它确定了四年（2018-2021 年）平均评级最高和最低的类别。这可以提供有价值的见解，了解哪些类别表现最佳，哪些类别表现不佳，从而有助于战略决策和投资分析。


CREATE DYNAMIC TABLE IF NOT EXISTS the_highest_and_lowest_average_ratings_over_years AS
SELECT * FROM (
    SELECT 'Category with maximum Rating :- ' || c.fund_category AS category, 
           ROUND(SUM(c.yr_18 + c.yr_19 + c.yr_20 + c.yr_21)  4, 2) AS avg_YoY
    FROM (
        SELECT b.fund_category,
               MAX(b.yr_2018) AS yr_18,
               MAX(b.yr_2019) AS yr_19,
               MAX(b.yr_2020) AS yr_20,
               MAX(b.yr_2021) AS yr_21
        FROM (
            SELECT a.fund_category,
                   CASE WHEN a.year = '2018' THEN a.avg_rating ELSE 0 END AS yr_2018,
………………

ALTER DYNAMIC TABLE the_highest_and_lowest_average_ratings_over_years 
SET COMMENT '四年（2018-2021 年）最高和最低平均评级 此查询的目的是计算每个基金类别的平均同比 (YoY) 评级。它确定了四年（2018-2021 年）平均评级最高和最低的类别。这可以提供有价值的见解，了解哪些类别表现最佳，哪些类别表现不佳，从而有助于战略决策和投资分析。';


ALTER DYNAMIC TABLE average_yoy_rating_for_funds 
SET COMMENT '每个基金的平均同比 (YoY) 评分 此查询根据基金的 fund_long_name 计算每个基金的平均同比 (YoY) 评分。它汇总了每年（2018、2019、2020 和 2021）的评分，并计算这些年的平均评分。从此分析得出的业务推论包括确定平均同比评分最高和最低的基金类别、突出显示不一致之处和趋势以及确定评分为 0 的类别。此信息对于了解各种基金类别随时间推移的业绩动态至关重要。';


ALTER DYNAMIC TABLE comparison_table_for_key_rating_kpis_yoy 
SET COMMENT '关键评级 KPI 同比 (YoY) 比较表 此查询生成关键评级 KPI 同比 (YoY) 比较表。KPI 包括：平均评级 (avg_rating)：衡量基金组合的整体表现。回报评级 (avg_return_rating)：评估基金产生的产出或回报程度。风险评级 (avg_risk_rating)：评估与基金相关的风险程度。查询根据基金的 fund_long_name 汇总每个基金的这些 KPI，并计算 2018、2019、2020 和 2021 年的平均值。';


ALTER DYNAMIC TABLE categorizing_funds_based_on_their_performance_and_risk_return_matrix 
SET COMMENT '根据基金的表现和风险回报矩阵对基金进行分类 此查询旨在通过根据基金的表现和风险回报矩阵对基金进行分类来确定绩效趋势。它有助于业务团队了解不同基金的表现及其相关的风险和回报特征。评级：反映基金的表现。风险评级：表示与基金相关的风险程度。回报评级：表示基金的产出或回报程度。该查询根据基金的平均评级将基金分为“表现良好”和“表现不佳”。它还将基金分为四个风险回报类别：高风险高回报、高风险低回报、低风险低回报和低风险高回报。';


ALTER DYNAMIC TABLE performance_and_risk_vs_return_matrix_analysis 
SET COMMENT '绩效和风险与回报矩阵分析 此查询评估各种共同基金的绩效和风险回报状况，以进行业务审查。它比较了表现良好和表现不佳的基金的数量，并将它们归类为不同的风险回报矩阵。';


ALTER DYNAMIC TABLE risk_vs_return_matrix_analysis_for_business_review 
SET COMMENT '业务审查的风险与回报矩阵分析。此查询分析各种共同基金的风险与回报矩阵，以帮助业务审查团队了解绩效趋势。重点是确定属于四类的基金：高风险高回报、低风险高回报、高风险低回报以及低风险低回报。业务推断是顶级组是低-低和高-高，检查点是评估低-低基金的数量，以确定对于普通客户来说低多少。';


ALTER DYNAMIC TABLE highest_turnover_analysis 
SET COMMENT '最高周转率分析，分析按基金全名、基金类别、投资类型和规模类型分类的共同基金的最大年度持股周转率。目标是确定哪个基金或类别的周转率最高，从而深入了解每个细分市场的流动性和活动水平。';


ALTER DYNAMIC TABLE average_expense_projections 
SET COMMENT '费用项目趋势-Average Expense Projections，分析并总结了不同时间范围内（3 年、5 年和 10 年）共同基金的费用预测。此分析有助于了解费用趋势并识别具有重大费用预测的基金家族。';


ALTER DYNAMIC TABLE expense_trend_analysis_by_fund_family 
SET COMMENT '费用项目趋势-Expense Trend Analysis by Fund Family，分析并总结了不同时间范围内（3 年、5 年和 10 年）共同基金的费用预测。此分析有助于了解费用趋势并识别具有重大费用预测的基金家族。';


ALTER DYNAMIC TABLE yoy_fund_expense_comparison 
SET COMMENT '费用项目趋势-Year-over-Year Fund Expense Comparison，分析并总结了不同时间范围内（3 年、5 年和 10 年）共同基金的费用预测。此分析有助于了解费用趋势并识别具有重大费用预测的基金家族。';

set cz.optimizer.mv.auto.unique.key.enabled = false;
refresh dynamic table the_highest_and_lowest_average_ratings_over_years;
refresh dynamic table average_yoy_rating_for_funds;
refresh dynamic table comparison_table_for_key_rating_kpis_yoy;
refresh dynamic table categorizing_funds_based_on_their_performance_and_risk_return_matrix;
refresh dynamic table performance_and_risk_vs_return_matrix_analysis;
refresh dynamic table risk_vs_return_matrix_analysis_for_business_review;
refresh dynamic table highest_turnover_analysis;
refresh dynamic table average_expense_projections;
refresh dynamic table expense_trend_analysis_by_fund_family;
refresh dynamic table yoy_fund_expense_comparison;