mysql - SQL 中的顺序分组依据

Question

我正在尝试使用 Herfindahl-Index 来计算投资组合的行业集中度在 SQL 中。我的数据包含基金在某​​个时间点持有的股票，其中每只股票都与行业相关，具体取决于发行公司的业务模式。我想实现公式 \sum_{i=1}^N s_i^2，请参阅维基百科。

我在 SQL Fiddle 上设置了一个带有玩具数据的最小示例。 .

SELECT
    fdate, fund, SUM(ind_share * ind_share) as hfi
FROM (
  SELECT 
      a.fdate, a.fund, a.industry, SUM(amount)/b.fund_size AS ind_share 
  FROM holdings a
  JOIN (
      SELECT
          fdate, fund, SUM(amount) AS fund_size
      FROM 
          holdings 
      GROUP BY
          fdate, fund) b 
  ON 
      a.fdate = b.fdate AND a.fund = b.fund 
  GROUP BY fdate, fund, industry) AS T
GROUP BY fdate, fund

这个解决方案很丑陋，因为它需要很多子查询。为了更简单的解决方案，我想顺序执行Group By或为每个组成员分配组总和。

显然，我的数据集要大得多，因此我想优化此代码。有没有更优雅的不使用子查询的解决方案？

谢谢。

Answer 1

这是尽可能简单/简洁的:

SELECT f.fdate, f.fund
   , SUM(POW(fundind_size/fund_size, 2)) as hfi
FROM  (
      SELECT fdate, fund, SUM(amount) AS fund_size
      FROM holdings 
      GROUP BY fdate, fund
  ) AS f
  JOIN 
  (
     SELECT fdate, fund, SUM(amount) fundind_size 
     FROM holdings
     GROUP BY fdate, fund, industry
  ) AS fi
  ON f.fdate = fi.fdate AND f.fund = fi.fund
GROUP BY f.fdate, f.fund
;

这与您当前所拥有的没有太大不同，仍然有三个单独的 GROUPing 和两个子查询；但此版本中的子查询并不那么“深”。

注意:最新版本的 MySQL 添加了“窗口”函数和聚合函数的“窗口化”。我对它们的需求不大，但在这种情况下它们可能会有所帮助。