PostgreSQL:SQL请求，带有一个组和两个不同表的百分比

Question

我目前在复杂的请求（加入）上被阻止：

我有此表"数据"：

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

和此表" 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中不可能调整Undivot和Pivot功能。

Answer 1

我会创建汇总每个表并计算您列出的出现的CTE，并在data的聚合上加入dico的CONTRECATION两次，一次用于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