时间戳

首页 > 解决方案 > 尽管使用了别名，但我从内部查询中找不到列

sql - 尽管使用了别名，但我从内部查询中找不到列

问题描述

我是 SQL 新手，我想基本上从我的查询中得出一个四分位图。但是，我的查询遇到了一些问题

我给了别名，我正在尝试计算四分位数。

这是查询

SELECT series,
       MIN(value) AS minimum,
       AVG(q1) AS q1,
       AVG(median) AS median,
       AVG(q3) AS q3,
       MAX(value) AS maximum
FROM 
    (SELECT series,
            PERCENTILE_CONT(0.25) WITHIN GROUP 
                 (ORDER BY value) OVER (PARTITION BY series) AS q1,
            MEDIAN(value) OVER (PARTITION BY series) AS median,
            PERCENTILE_CONT(0.75) WITHIN GROUP 
                 (ORDER BY value) OVER (PARTITION BY series) AS q3
     FROM  
         (SELECT type as series, (a.duration)/(60000) as value
          FROM test.pattern a
          JOIN test.metadata b ON a.id = b.id) AS quartile) AS raw_data
GROUP BY 1

但是我不断得到

column "value" does not exist in raw_data

我的列名没有任何大写字母，现在这真的让我很困惑。

我将不胜感激

更新： 感谢 Gordan，查询正在运行。但是对于大型数据集，执行时间真的很慢，任何关于如何改进的指针谢谢

标签： sqlpostgresqlamazon-redshift

解决方案

您还需要在中间子查询中选择它：

FROM (SELECT series, value,
---------------------^
             PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY series) AS q1,
             MEDIAN(value) OVER (PARTITION BY series) AS median,
             PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY series) AS q3
      FROM (SELECT type as series, (a.duration)/(60000) as value
                FROM test.pattern p JOIN
                     test.metadata m
                     ON p.id = m.id
          ) AS quartile
     ) AS raw_data

推荐阅读