时间戳

首页 > 解决方案 > 蜂巢查询中的 STDDEV 函数

sql - 蜂巢查询中的 STDDEV 函数

问题描述

我STDDEV在下面的查询中使用函数时遇到问题。内部子查询工作正常，但是当我运行整个查询时出现错误：

表达式不在GROUP BY键中NO_OF_LBR_ONLY_CLAIMS

请帮忙。

SELECT C.NO_OF_LBR_ONLY_CLAIMS,
       C.TOTAL_CLAIMS_FOR_DEALER,
       C.BAC,
       C.COUNTRY_CD,
       C.PCT_OF_LBR_ONLY_CLAIMS,
       C.AVG_PCT_LOC_FOR_ALL_DEALERS,
       STDDEV(C.PCT_OF_LBR_ONLY_CLAIMS) AS STD_DEV,
       (C.PCT_OF_LBR_ONLY_CLAIMS - C.AVG_PCT_LOC_FOR_ALL_DEALERS)/STDDEV(C.PCT_OF_LBR_ONLY_CLAIMS) AS RI_1
FROM
  (SELECT A.NO_OF_LBR_ONLY_CLAIMS,
          A.TOTAL_CLAIMS_FOR_DEALER,
          A.BAC,
          A.COUNTRY_CD,
          A.PCT_OF_LBR_ONLY_CLAIMS,
          B.AVG_PCT_LOC_FOR_ALL_DEALERS
   FROM DBO.RISK_IND_01_TMP1 AS A
   LEFT JOIN
     (SELECT SUM(PCT_OF_LBR_ONLY_CLAIMS)/COUNT(DISTINCT BAC) AS AVG_PCT_LOC_FOR_ALL_DEALERS,
             COUNTRY_CD
      FROM DBO.RISK_IND_01_TMP1
      GROUP BY COUNTRY_CD) AS B ON A.COUNTRY_CD = B.COUNTRY_CD) C

标签： sqlhive

解决方案

如果您希望在每一行上复制整个集合的标准偏差，请使用窗口函数：

   STDDEV(C.PCT_OF_LBR_ONLY_CLAIMS) OVER () AS STD_DEV,

推荐阅读