如何通过两个表将元组的特定值与SQL中的AVG值进行比较

Question

我正在搜索一个查询，可以将AVG值与所有元组的特定值"连接"在一起。

因此，我有以下数据（主题是关于公司的数据集）：

表1：ID名称国家类别

表2：ID值1值2

我设法获得了一家公司特定国家和类别的AVG值Value1/Value2。现在，我试图显示一个国家/类别的公司数量，这些公司在其类别的AVG值的5%范围内。我尝试了所有我能想象到的可能性。我遇到的问题是，在嵌套查询中不可能返回多个值。

有人能给我一个解决这个问题的方法吗？

Answer 1

我认为您需要这样的东西：

with data as (
  select cname, country, class, value1/value2 vv
      , avg(value1/value2) over (partition by country, class) av
    from vals join companies using (id) )
select country, class, count(case when vv/av < 0.2 then 1 end) cnt
  from data group by country, class

SQLFiddle

它是如何工作的？在每个id的第一步中，我们收集按国家和阶级划分的平均值（value1/value2）的信息。这是子查询data中的列av，分析函数avg()用于计算。此子查询的输出为：

CNAME      COUNTRY    CLASS         VV         AV
---------- ---------- ----- ---------- ----------
GHI        China      A             30         30
ABC        Russia     A              5       32,5
JKL        Russia     A             60       32,5
DEF        Russia     B              3          3

接下来，我们只需要按国家和类别对数据进行分组，并附上符合您标准的公司数量的信息。所以完整查询的输出是（我使用了20%，将条件vv/av < 0.2更改为您需要的任何内容）：

COUNTRY    CLASS        CNT
---------- ----- ----------
Russia     A              1
China      A              0
Russia     B              0

Answer 2

您可以使用AVG()作为窗口（分析）函数：

SELECT id, name, country, class, company_value FROM (
    SELECT t1.id, t1.name, t1.country, t1.class, t2.value1/t2.value2 AS company_value
         , AVG(t2.value1/t2.value2) OVER ( PARTITION BY t1.country, t1.class ) AS avg_value
      FROM table1 t1 INNER JOIN table2 t2
        ON t1.id = t2.id
) WHERE company_value BETWEEN avg_value * 0.95 AND avg_value * 1.05;

在上述查询中，我将0.95和1.05分别用于1 - 0.05和1 + 0.05（5%以内）。要获得计数，只需将上面的内容用作子查询（或者CTE，如果你对此更满意的话）：

SELECT country, class, COUNT(*) FROM (
    SELECT id, name, country, class, company_value FROM (
        SELECT t1.id, t1.name, t1.country, t1.class, t2.value1/t2.value2 AS company_value
             , AVG(t2.value1/t2.value2) OVER ( PARTITION BY t1.country, t1.class ) AS avg_value
          FROM table1 t1 INNER JOIN table2 t2
            ON t1.id = t2.id
    ) WHERE company_value BETWEEN avg_value * 0.95 AND avg_value * 1.05
);