sql—将多个group by查询合并到单个查询中

kxkpmulp 于 2021-06-25 发布在 Hive

关注(0)|答案(2)|浏览(346)

我有一个结构类似于下面的表，我有四个分组变量（region、site、method、client）和三个度量，每个度量都有一个附带的“bucket”列，给出度量的五分位数。我想通过分组变量和五分位桶来执行一些聚合。现在我必须运行三个独立的查询，并尝试连接结果，这将导致重复。有没有办法将下面的三个查询合并为一个查询？

Select Region,Site,Method,Client,A_bucket,
count(ID) as nA,
avg(Metric_A) as Avg.A,
min(Metric_A) as Min.A
From S_overflow
Group By Region,Site,Method,Client,A_bucket

和

Select Region,Site,Method,Client,B_bucket,
count(ID) as nB,
avg(Metric_B) Avg.B,
min(Metric_B) Min.B
From S_overflow
Group By Region,Site,Method,Client,B_bucket

和

Select Region,Site,Method,Client,C_bucket,
count(ID) as nC,
avg(Metric_C) as avg.C,
min(Metric_C) as Min.C
From S_overflow
Group By Region,Site,Method,Client,C_bucket

下面是我在r中通过按区域、站点、方法、客户机、bucket和绑定列对每个结果进行排序得到的所需结果，但是我需要一个hql（sql）解决方案，最好将三个查询减少到一个。
期望的结果

+--------+---------------+--------+--------+-----------+----+-------------+-------+-----------+----+-------------+-------+-----------+----+-------------+-------+
| Region |     Site      | Method | Client | A_buckets | nA |    Avg.A    | min.A | B_buckets | nB |    Avg.B    | min.B | C_buckets | nC |    avg.C    | min.C |
+--------+---------------+--------+--------+-----------+----+-------------+-------+-----------+----+-------------+-------+-----------+----+-------------+-------+
| A      | Local         |      1 | mno    |         1 |  2 |        73.5 |    22 |         1 |  2 |         153 |    49 |         1 |  2 |        80.5 |    49 |
| A      | Local         |      1 | mno    |         2 |  2 |       343.5 |   302 |         2 |  2 |         420 |   363 |         2 |  2 |         395 |   355 |
| A      | Local         |      1 | mno    |         3 |  2 |       495.5 |   415 |         3 |  2 |       682.5 |   631 |         3 |  2 |       564.5 |   485 |
| A      | Local         |      1 | mno    |         4 |  2 |       585.5 |   585 |         4 |  2 |         795 |   762 |         4 |  2 |       696.5 |   677 |
| A      | Local         |      1 | mno    |         5 |  2 |         751 |   600 |         5 |  2 |       946.5 |   908 |         5 |  2 |         848 |   717 |
| A      | Local         |      1 | rst    |         1 |  2 |        24.5 |    21 |         1 |  2 |       241.5 |   203 |         1 |  2 |       156.5 |    97 |
| A      | Local         |      1 | rst    |         2 |  2 |       232.5 |   193 |         2 |  2 |       360.5 |   316 |         2 |  2 |         280 |   234 |
| A      | Local         |      1 | rst    |         3 |  2 |       421.5 |   377 |         3 |  2 |       465.5 |   438 |         3 |  2 |         374 |   331 |
| A      | Local         |      1 | rst    |         4 |  2 |       647.5 |   602 |         4 |  2 |       690.5 |   687 |         4 |  2 |         542 |   466 |
| A      | Local         |      1 | rst    |         5 |  2 |       937.5 |   937 |         5 |  2 |       956.5 |   924 |         5 |  2 |         797 |   691 |
| A      | Local         |      1 | xyz    |         1 |  2 |         209 |    70 |         1 |  2 |         224 |    80 |         1 |  2 |       159.5 |   148 |
| A      | Local         |      1 | xyz    |         2 |  2 |         477 |   382 |         2 |  2 |         425 |   410 |         2 |  2 |       232.5 |   205 |
| A      | Local         |      1 | xyz    |         3 |  2 |       611.5 |   596 |         3 |  2 |         521 |   444 |         3 |  2 |       315.5 |   287 |
| A      | Local         |      1 | xyz    |         4 |  2 |         677 |   634 |         4 |  2 |       726.5 |   628 |         4 |  2 |         375 |   367 |
| A      | Local         |      1 | xyz    |         5 |  2 |       829.5 |   807 |         5 |  2 |         843 |   838 |         5 |  2 |       671.5 |   570 |
| B      | International |      2 | mno    |         1 |  3 | 175.6666667 |    15 |         1 |  3 |         114 |    58 |         1 |  3 |         202 |   159 |
| B      | International |      2 | mno    |         2 |  2 |       515.5 |   511 |         2 |  2 |         298 |   245 |         2 |  2 |         394 |   370 |
| B      | International |      2 | mno    |         3 |  3 | 614.3333333 |   573 |         3 |  3 |         569 |   508 |         3 |  3 | 493.3333333 |   430 |
| B      | International |      2 | mno    |         4 |  2 |       715.5 |   708 |         4 |  2 |       786.5 |   686 |         4 |  2 |         737 |   627 |
| B      | International |      2 | mno    |         5 |  2 |         814 |   760 |         5 |  2 |         941 |   901 |         5 |  2 |       861.5 |   847 |
| B      | International |      2 | rst    |         1 |  3 |          77 |    26 |         1 |  3 | 133.3333333 |     3 |         1 |  3 |          43 |    12 |
| B      | International |      2 | rst    |         2 |  2 |         329 |   225 |         2 |  2 |       262.5 |   243 |         2 |  2 |         259 |   192 |
| B      | International |      2 | rst    |         3 |  3 | 522.6666667 |   450 |         3 |  3 |         505 |   380 |         3 |  3 | 465.3333333 |   336 |
| B      | International |      2 | rst    |         4 |  2 |         701 |   578 |         4 |  2 |       713.5 |   673 |         4 |  2 |         790 |   755 |
| B      | International |      2 | rst    |         5 |  2 |         948 |   904 |         5 |  2 |       929.5 |   898 |         5 |  2 |       846.5 |   834 |
| B      | International |      2 | xyz    |         1 |  3 | 119.6666667 |   101 |         1 |  3 | 170.3333333 |   125 |         1 |  3 | 259.3333333 |    43 |
| B      | International |      2 | xyz    |         2 |  3 |         373 |   282 |         2 |  3 | 279.3333333 |   207 |         2 |  3 | 695.3333333 |   601 |
| B      | International |      2 | xyz    |         3 |  2 |         493 |   492 |         3 |  2 |         476 |   462 |         3 |  2 |         805 |   804 |
| B      | International |      2 | xyz    |         4 |  3 | 676.3333333 |   552 |         4 |  3 | 554.3333333 |   511 |         4 |  3 |         850 |   820 |
| B      | International |      2 | xyz    |         5 |  2 |         896 |   868 |         5 |  2 |       770.5 |   722 |         5 |  2 |       929.5 |   906 |
+--------+---------------+--------+--------+-----------+----+-------------+-------+-----------+----+-------------+-------+-----------+----+-------------+-------+

原始数据

+--------+---------------+--------+--------+----+----------+-----------+----------+-----------+----------+-----------+
| Region |     Site      | Method | Client | ID | Metric_A | A_buckets | Metric_B | B_buckets | Metric_C | C_buckets |
+--------+---------------+--------+--------+----+----------+-----------+----------+-----------+----------+-----------+
| A      | Local         |      1 | mno    |  3 |      125 |         1 |      257 |         1 |      677 |         4 |
| A      | Local         |      1 | mno    |  6 |      585 |         4 |      762 |         4 |      644 |         3 |
| A      | Local         |      1 | mno    |  9 |       22 |         1 |      631 |         3 |      717 |         5 |
| A      | Local         |      1 | mno    | 12 |      576 |         3 |      363 |         2 |      112 |         1 |
| A      | Local         |      1 | mno    | 15 |      586 |         4 |      985 |         5 |      435 |         2 |
| A      | Local         |      1 | mno    | 18 |      902 |         5 |      828 |         4 |      716 |         4 |
| A      | Local         |      1 | mno    | 21 |      302 |         2 |       49 |         1 |      355 |         2 |
| A      | Local         |      1 | mno    | 24 |      600 |         5 |      477 |         2 |      485 |         3 |
| A      | Local         |      1 | mno    | 27 |      415 |         3 |      908 |         5 |      979 |         5 |
| A      | Local         |      1 | mno    | 30 |      385 |         2 |      734 |         3 |       49 |         1 |
| A      | Local         |      1 | rst    |  2 |       21 |         1 |      203 |         1 |      234 |         2 |
| A      | Local         |      1 | rst    |  5 |      937 |         5 |      438 |         3 |      618 |         4 |
| A      | Local         |      1 | rst    |  8 |       28 |         1 |      924 |         5 |       97 |         1 |
| A      | Local         |      1 | rst    | 11 |      377 |         3 |      989 |         5 |      326 |         2 |
| A      | Local         |      1 | rst    | 14 |      602 |         4 |      687 |         4 |      216 |         1 |
| A      | Local         |      1 | rst    | 17 |      693 |         4 |      405 |         2 |      903 |         5 |
| A      | Local         |      1 | rst    | 20 |      938 |         5 |      316 |         2 |      466 |         4 |
| A      | Local         |      1 | rst    | 23 |      193 |         2 |      280 |         1 |      331 |         3 |
| A      | Local         |      1 | rst    | 26 |      272 |         2 |      694 |         4 |      691 |         5 |
| A      | Local         |      1 | rst    | 29 |      466 |         3 |      493 |         3 |      417 |         3 |
| A      | Local         |      1 | xyz    |  1 |      572 |         2 |      848 |         5 |      148 |         1 |
| A      | Local         |      1 | xyz    |  4 |      627 |         3 |      628 |         4 |      287 |         3 |
| A      | Local         |      1 | xyz    |  7 |      596 |         3 |      825 |         4 |      383 |         4 |
| A      | Local         |      1 | xyz    | 10 |      807 |         5 |      410 |         2 |      367 |         4 |
| A      | Local         |      1 | xyz    | 13 |      382 |         2 |      368 |         1 |      260 |         2 |
| A      | Local         |      1 | xyz    | 16 |      852 |         5 |      598 |         3 |      570 |         5 |
| A      | Local         |      1 | xyz    | 19 |      634 |         4 |      444 |         3 |      773 |         5 |
| A      | Local         |      1 | xyz    | 22 |      720 |         4 |       80 |         1 |      344 |         3 |
| A      | Local         |      1 | xyz    | 25 |      348 |         1 |      838 |         5 |      205 |         2 |
| A      | Local         |      1 | xyz    | 28 |       70 |         1 |      440 |         2 |      171 |         1 |
| B      | International |      2 | mno    | 33 |      723 |         4 |      508 |         3 |      418 |         2 |
| B      | International |      2 | mno    | 36 |      868 |         5 |      686 |         4 |      159 |         1 |
| B      | International |      2 | mno    | 39 |      352 |         1 |      196 |         1 |      208 |         1 |
| B      | International |      2 | mno    | 42 |      520 |         2 |      887 |         4 |      847 |         4 |
| B      | International |      2 | mno    | 45 |      708 |         4 |       88 |         1 |      876 |         5 |
| B      | International |      2 | mno    | 48 |      511 |         2 |      351 |         2 |      370 |         2 |
| B      | International |      2 | mno    | 51 |      160 |         1 |      607 |         3 |      847 |         5 |
| B      | International |      2 | mno    | 54 |      577 |         3 |      245 |         2 |      239 |         1 |
| B      | International |      2 | mno    | 57 |      693 |         3 |      901 |         5 |      430 |         3 |
| B      | International |      2 | mno    | 60 |      760 |         5 |      592 |         3 |      627 |         4 |
| B      | International |      2 | mno    | 63 |      573 |         3 |       58 |         1 |      571 |         3 |
| B      | International |      2 | mno    | 66 |       15 |         1 |      981 |         5 |      479 |         3 |
| B      | International |      2 | rst    | 32 |      992 |         5 |      898 |         5 |      834 |         5 |
| B      | International |      2 | rst    | 35 |      578 |         4 |      754 |         4 |      336 |         3 |
| B      | International |      2 | rst    | 38 |      555 |         3 |      623 |         3 |       67 |         1 |
| B      | International |      2 | rst    | 41 |      824 |         4 |        3 |         1 |      755 |         4 |
| B      | International |      2 | rst    | 44 |       77 |         1 |      961 |         5 |      825 |         4 |
| B      | International |      2 | rst    | 47 |       26 |         1 |      380 |         3 |      412 |         3 |
| B      | International |      2 | rst    | 50 |      904 |         5 |      203 |         1 |       12 |         1 |
| B      | International |      2 | rst    | 53 |      433 |         2 |      282 |         2 |       50 |         1 |
| B      | International |      2 | rst    | 56 |      128 |         1 |      512 |         3 |      859 |         5 |
| B      | International |      2 | rst    | 59 |      563 |         3 |      194 |         1 |      326 |         2 |
| B      | International |      2 | rst    | 62 |      225 |         2 |      673 |         4 |      192 |         2 |
| B      | International |      2 | rst    | 65 |      450 |         3 |      243 |         2 |      648 |         3 |
| B      | International |      2 | xyz    | 31 |      101 |         1 |      185 |         1 |      804 |         3 |
| B      | International |      2 | xyz    | 34 |      127 |         1 |      125 |         1 |      701 |         2 |
| B      | International |      2 | xyz    | 37 |      492 |         3 |      462 |         3 |      601 |         2 |
| B      | International |      2 | xyz    | 40 |      924 |         5 |      612 |         4 |      906 |         5 |
| B      | International |      2 | xyz    | 43 |      786 |         4 |      722 |         5 |      784 |         2 |
| B      | International |      2 | xyz    | 46 |      442 |         2 |      511 |         4 |       43 |         1 |
| B      | International |      2 | xyz    | 49 |      552 |         4 |      207 |         2 |      820 |         4 |
| B      | International |      2 | xyz    | 52 |      868 |         5 |      819 |         5 |      953 |         5 |
| B      | International |      2 | xyz    | 55 |      494 |         3 |      540 |         4 |      855 |         4 |
| B      | International |      2 | xyz    | 58 |      131 |         1 |      267 |         2 |      875 |         4 |
| B      | International |      2 | xyz    | 61 |      282 |         2 |      201 |         1 |      806 |         3 |
| B      | International |      2 | xyz    | 64 |      395 |         2 |      364 |         2 |      156 |         1 |
| B      | International |      2 | xyz    | 67 |      691 |         4 |      490 |         3 |      579 |         1 |
+--------+---------------+--------+--------+----+----------+-----------+----------+-----------+----------+-----------+

sql Hive hiveql hql

来源：https://stackoverflow.com/questions/58704072/combine-multiple-group-by-queries-into-single-query

2条答案

按热度按时间

628mspwn1#

尝试使用交叉连接

select t1.Region, t1.Site, t1.Method, t1.Client, t1.A_bucket, count(t1.ID)
    , t2.B_bucket, t2.Metric_B
    , t3.C_bucket, t3.Metric_C
from 
    (select Region,Site,Method,Client,A_bucket, count(ID)
        , avg(Metric_A), min(Metric_A) as   Metric_A
    from S_overflow
    group By Region,Site,Method,Client,A_bucket) t1
cross join
    (select Region,Site,Method,Client,B_bucket, min(Metric_B)   as Metric_B
    from S_overflow
    group By Region,Site,Method,Client,B_bucket) t2
cross join
    (select Region,Site,Method,Client,C_bucket, min(Metric_C)   as Metric_C
    from S_overflow
    group By Region,Site,Method,Client,C_bucket) t3

赞(0）回复(0）举报 2021-06-26

6rvt4ljy2#

在看到@metal的建议并检查了我的代码之后，我意识到解决方案就在眼前，这个代码适合我。

select t1.Region, t1.Site, t1.Method, t1.Client, t1.A_buckets, t1.A_cnt,t1.avg_A,t1.min_A,
     t2.B_buckets, t2.B_cnt, t2.min_B,t2.avg_B,
    t3.C_buckets, t3.C_cnt,t3.min_C, t3.avg_C
from 
    (select Region,Site,Method,Client,A_buckets, count(ID) as A_cnt,
         avg(Metric_A) as avg_A, min(Metric_A) as min_A   
    from test3
    group By Region,Site,Method,Client,A_buckets) t1
 join
    (select Region,Site,Method,Client,B_buckets, count(ID) as B_cnt, min(Metric_B) as min_B,avg(Metric_B)as avg_B  
    from test3
    group By Region,Site,Method,Client,B_buckets) t2
    on t1.Region=t2.Region 
    and t1.Site=t2.Site and t1.Method=t2.Method and t1.client=t2.Client and t1.A_buckets=t2.B_buckets
join
    (select Region,Site,Method,Client,C_buckets, count(ID) as C_cnt, min(Metric_C) as min_C,avg(Metric_C)as avg_C
    from test3
    group By Region,Site,Method,Client,C_buckets) t3
    on t2.Region=t3.Region 
    and t2.Site=t3.Site and t2.Method=t3.Method and t2.client=t3.Client and t2.B_buckets=t3.C_buckets

赞(0）回复(0）举报 2021-06-26

我来回答

sql—将多个group by查询合并到单个查询中

2条答案

相关问题

热门标签

最新问答