WAQ动态地代表SalesRep的季度销售额(使用Pivot)

Question

编写一个查询，以以下方式列出SalesRep及其Sales(输出中的列(

SalesRep Name, City, Q1Sales, Q2Sales, Q3Sales, Q4Sales, QAvg, YearSales
Where QAvg = (Q1 Sales + Q2 Sales + Q3 Sales + Q4 Sales) / 4
And YearSales = Q1 Sales + Q2 Sales + Q3 Sales + Q4 Sales

表格内容：

SalesRep    
--------------------------------------
 column name | data type | constraint
--------------------------------------
 Id          | Int       | PK
 Name        | Varchar   |
 City        | Varchar   |    

SampleData
--------------------------
 Id | Name    | City
--------------------------
 1  | Agent A | Chennai
 2  | Agent B | Delhi
 3  | Agent C | Pune
 4  | Agent D | Bangalore
 5  | Agent E | Patna

Sales
-----------------------------------------------------
 column name | data type     | constraint
-----------------------------------------------------
 SalesRepId  | Int           |  FK to Salesrep table
 QtrId       | Int           |  FK to Qtr table
 Sales       | Numeric(10,2) |

Sample Data
----------------------------
 SalesRepId | QtrId | Sales
----------------------------
 1          | 1     | 1000
 1          | 2     | 2500
 2          | 1     | 3450
 2          | 2     | 4300
 2          | 3     | 230
 2          | 4     | 1367
 4          | 3     | 500
 4          | 4     | 750
 5          | 4     | 450

Qtr
--------------------------------------
 column name | date type | constraint
--------------------------------------
 QtrId       | Int       | PK
 Desc        | varchr2   |

Sample Data
-------------------
  QtrId | Desc1
-------------------
   1    | Q1 FY 14
   2    | Q2 FY 14
   3    | Q3 FY 14
   4    | Q4 FY 14

我使用pivot实现了它，但我的查询不是动态的。我必须让它充满活力，但怎么做呢？

我的问题是：

SELECT name, city, 
       "Q1 FY 14", "Q2 FY 14", "Q3 FY 14", "Q4 FY 14", 
       qavg, year_sales
  FROM (SELECT ID, name, qtrid, sales, city
          FROM salesrep
          left outer join sales
            on salesrep.id=sales.salesrepid)
 PIVOT ( 
     sum(sales) for
          qtrid IN (1 AS "Q1 FY 14",
                    2 AS "Q2 FY 14",
                    3 AS "Q3 FY 14",
                    4 AS "Q4 FY 14") 
 ) T1
LEFT OUTER JOIN  ( select SALESREPID, 
                          sum(sales)/count(distinct qtrid) as qavg,
                          SUM(SALES) AS year_sales
                     FROM SALES 
                    GROUP BY SALESREPID
                 ) T2
ON T1.ID = T2.SALESREPID;

Answer 1

select year, id, name, city, q1, q2, q3, q4,
    (nvl(q1, 0)+nvl(q2, 0)+nvl(q3, 0)+nvl(q4, 0)) 
      / (nvl2(q1,1,0)+nvl2(q2,1,0)+nvl2(q3,1,0)+nvl2(q4,1,0)) s_avg,
    (nvl(q1, 0)+nvl(q2, 0)+nvl(q3, 0)+nvl(q4, 0)) / 4 s_avg4,
    nvl(q1, 0)+nvl(q2, 0)+nvl(q3, 0)+nvl(q4, 0) s_sum
  from (
    select substr(q.descr, 1, 2) qtr, substr(q.descr, 7, 2) year, 
           sr.id, sr.name, sr.city, s.sales
      from salesrep sr join sales s on s.salesrepid = sr.id 
      join qtr q on q.qtrid = s.qtrid)
  pivot (sum(sales) for qtr in ('Q1' Q1, 'Q2' Q2, 'Q3' Q3, 'Q4' Q4))
  order by year, id
==============================================================================
YEAR ID NAME       CITY          Q1    Q2    Q3    Q4    S_AVG   S_AVG4  S_SUM
---- -- ---------- ---------- ----- ----- ----- ----- -------- -------- ------
14    1 Agent A    Chennai     1000  2500                 1750      875   3500
14    2 Agent B    Delhi       3450  4300   230  1367  2336,75  2336,75   9347
14    4 Agent D    Bangalore                500   750      625    312,5   1250
14    5 Agent E    Patna                          450      450    112,5    450
15    1 Agent A    Chennai     4455                       4455  1113,75   4455

1( 您的部分问题是：其中QAvg=(第一季度销售额+第二季度销售额+三季度销售额+四季度销售额(/4。我认为你应该总是除以4——就像我在列S_AVG4中所做的那样。但是，如果要除以sales之和不为null的四分之一数，则使用列S_AVG的语法。

2( 在SQL查询中，解析时必须知道列数。动态解决方案有PLSQL解决方案，我已经在评论中发布了链接。