sql - PostgreSQL : SQL Request with a Group By and a Percentage on two differents tables

Question

我目前因一个复杂的请求(带有连接)而被阻止:

我有这张表“DATA”:

order | product
----------------
  1   |  A 
  1   |  B  
  2   |  A  
  2   |  D   
  3   |  A  
  3   |  C
  4   |  A  
  4   |  B
  5   |  Y  
  5   |  Z
  6   |  W  
  6   |  A
  7   |  A

还有这张 table “DICO”:

    order | couple | first | second
    -------------------------------
      1   |  A-B   |   A   |   B
      2   |  A-D   |   A   |   D
      3   |  A-C   |   A   |   C
      4   |  A-B   |   A   |   B
      5   |  Y-Z   |   Y   |   Z
      6   |  W-A   |   W   |   A

我想在一行中获得:

order | count | total1stElem | %1stElem | total2ndElem | %2ndElem
------------------------------------------------------------------
A-B   |  2    |       6      |    33%   |     2        |    100%       
A-D   |  1    |       6      |    16%   |     1        |    100%      
A-C   |  1    |       6      |    16%   |     1        |    100%         
Y-Z   |  1    |       1      |    100%  |     1        |    100%     
W-A   |  1    |       1      |    100%  |     6        |    16%     

信息:

Fields: (On the 1st Line example)
  total1stElem : count ALL('A') in table Data (all occurrences of A in Data) 
  total2ndElem : count ALL('B') in table Data (all occurrences of B in Data)
  Count : count the number of 'A-B' occurence in table Dico
  %1stElem = ( Count / total1stElem ) * 100
  %1ndElem = ( Count / total2ndElem ) * 100

我是根据这个要求:

select couple, count(*),
       sum(count(*)) over (partition by first) as total,
       (count(*) * 1.0 / sum(count(*)) over (partition by first) ) as ratio
from dico1
group by couple, first ORDER BY ratio DESC;

我想做类似的事情:

select couple, count(*) as COUNT,
       count(*) over (partition by product #FROM DATA WHERE product = first#) as total1stElem,
       (count(*) * 1.0 / sum(count(*)) over (partition by product #FROM DATA WHERE product = first#) as %1stElem
       count(*) over (partition by product #FROM DATA WHERE product = second#) as total2ndElem,
       (count(*) * 1.0 / sum(count(*)) over (partition by product #FROM DATA WHERE product = second#) as %2ndElem
from dico1
group by couple, first ORDER BY COUNT DESC;

我在请求的接合部分完全受阻。有人可以帮助我吗？我在 Oracle 上得到过这种帮助或请求，但不幸的是，在 PostgreSQL 中无法适配 UNPIVOT 和 PIVOT 函数。

Answer 1

我会创建 CTE 来聚合每个表并计算您列出的出现次数，然后将 dico 的聚合加入到 data 的聚合中两次，一次用于 first 和一次 second:

WITH data_count AS (
    SELECT   product, COUNT(*) AS product_count
    FROM     data
    GROUP BY product
),
dico_count AS (
    SELECT   couple, first, second, COUNT(*) AS dico_count
    FROM     dico
    GROUP BY couple, first, second
)
SELECT   couple,
         dico_count,
         data1.product_count AS total1stElem,
         TRUNC(dico_count * 100.0 / data1.product_count) AS percent1stElem,
         data2.product_count AS total2ndElem,
         TRUNC(dico_count * 100.0 / data2.product_count) AS percent2ndElem
FROM     dico_count dico
JOIN     data_count data1 ON dico.first = data1.product
JOIN     data_count data2 ON dico.second = data2.product
ORDER BY 1