将百分比更改和历史值范围添加到数据透视查询的结果中

[英]Add percentage change and range of historical values into results of Pivot query

本文翻译自 alwaysVBNET 查看原文 2017/08/25 78 tsql/ sql-server/ sql

I have two tables: Race and Odds. One race has many odds including historical odds for each horse. In my pivot table I show the average of the historical prices for each horse for each website.

我有两张桌子：种族和赔率。一场比赛有很多赔率，包括每匹马的历史赔率。在我的数据透视表中，我显示了每个网站每匹马的历史价格平均值。

What I want is to add a two more columns: a column that calculates the percentage change (% change = (first odd-last odd) ÷ first odd × 100) and a column that includes all the historical odds in a field. I am not sure how to include the data for these two fields in aggregation. Any ideas?

我想要的是增加两列：一列计算百分比变化（％变化=（第一个奇数 - 最后一个奇数）÷第一个奇数×100）和一个包含一个字段中所有历史赔率的列。我不确定如何在聚合中包含这两个字段的数据。有任何想法吗？

Desired results

期望的结果

|     Name | OddsRaceID |    ABC |    DEF |    GHI |    IJK |    OPQ |  %  | All HistorOdds|
|----------|------------|--------|--------|--------|--------|--------|-----|---------------|
|   Jonner |          1 |  2.032 | (null) | (null) | (null) | (null) |13.3%| 1.50,1.60,1.70|
|   Seller |          1 | (null) | (null) | (null) | (null) | (null) |-63.6% | 11,12,4       |
| Show off |          1 | (null) | (null) | (null) | 5.4166 | (null) | ..%  | 9,3,4         |
|  Stander |          1 | (null) | 1.5933 | (null) | (null) | (null) | ..% | 1.40          |
|    Super |          1 | (null) | (null) |    5.2 | (null) | (null) | ..% | 5,5.5,6.5     |
|    Taryn |          1 | (null) | (null) | (null) | (null) |   11.5 | ..% | 15,12         |

Here is the SQL Fiddle

这是SQL小提琴

2 个解决方案

#1

EDIT - Corrected for Typo

编辑 - 更正错字

Here is one option using a subquery and a simple join

这是使用子查询和简单连接的一个选项

Example

例

SELECT piv1.* 
      ,[%Change]   = format((B.Odd2-B.Odd1) / B.Odd1,'0.0%')
      ,AllHistOdds = B.Hist
FROM
    (SELECT * FROM
        (SELECT Name, AVG(Odds) AS Odds, WebsiteName,OddsRaceID FROM ArbiOdds GROUP BY WebsiteName, Name, OddsRaceID) AS SourceTable
            PIVOT 
            (
                 AVG(Odds) 
                 FOR WebsiteName IN
                 ([ABC],[DEF],[GHI],[IJK],[OPQ])
            ) as bob
     ) piv1 
 Join (
        Select Name
              ,OddsID
              ,Odd1 = max(case when RN1=1 then Odds end) 
              ,Odd2 = max(case when RN2=1 then Odds end)
              ,Hist = Stuff((Select ',' +cast(Odds as varchar(25)) From ArbiOdds Where Name=O.Name and OddsID=O.OddsID Order By DateUTC For XML Path   ('')),1,1,'')
         From (
                Select Name
                      ,Odds
                      ,OddsID
                      ,RN1 = Row_Number() over (Partition By Name,OddsID Order By DateUTC) 
                      ,RN2 = Row_Number() over (Partition By Name,OddsID Order By DateUTC Desc) 
                 from ArbiOdds
              ) O
         Group By Name,OddsID
      ) B on B.Name=piv1.name and B.OddsID=piv1.OddsRaceID
ORDER BY piv1.name

Returns

#2

Try this:

尝试这个：

SELECT A.*,
       B.Percentage as '%Change',
       C.All_HistorOdds
FROM

(
SELECT piv1.*
FROM
    (SELECT * FROM
        (SELECT Name, AVG(Odds) AS Odds, WebsiteName,OddsRaceID FROM ArbiOdds GROUP BY WebsiteName, Name, OddsRaceID) AS SourceTable
            PIVOT 
            (
                 AVG(Odds) 
                 FOR WebsiteName IN
                 ([ABC],[DEF],[GHI],[IJK],[OPQ])
            ) as bob
     ) piv1 
) as A

INNER JOIN 
(

SELECT ROUND(((first_odd-last_odd)*1.0/first_odd) * 100,1) as Percentage,FIRST.Name
FROM
(
SELECT odds as first_odd,Name
FROM 
    (
        SELECT odds,Name, ROW_NUMBER()OVER(PARTITION BY oddsID,NAME ORDER BY DateUTC ASC) as rownumber
    FROM ArbiOdds
    WHERE OddsID = 1
    ) as GET_FIRSTODD
WHERE GET_FIRSTODD.rownumber = 1
) as FIRST

INNER JOIN 
(SELECT odds as last_odd,Name 
FROM 
    (
    SELECT odds,Name, ROW_NUMBER()OVER(PARTITION BY oddsID,NAME ORDER BY DateUTC DESC) as rownumber
    FROM ArbiOdds
       WHERE OddsID = 1
    ) as GET_FIRSTODD
WHERE GET_FIRSTODD.rownumber = 1
) as LAST
ON LAST.Name = FIRST.Name


) as B

ON B.Name = A.Name


INNER JOIN 

(
SELECT Name,STUFF((
SELECT ',' + cAST(Odds as varchar(50))
              FROM ArbiOdds as A
              WHERE OddsID = 1 AND A.Name = B.Name
              FOR XML PATH ('')),1,1,'') as 'All_HistorOdds'
FROM ArbiOdds as B
GROUP BY Name
) as C

ON C.Name = A.Name

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2017/08/25/3a4897230af8b2a4a0961aa4bf4e5e37.html。

猜您在找

SQL - 如何计算百分比并添加到查询结果 - SQL - How to compute percentage and add to query result 更改百分比范围qtm生成的地图 - Change percentage range qtm-generated map 将百分比添加到数字计数器javascript - add percentage to a number counter javascript 如何通过MySQL中结果总数的百分比来限制查询结果？ - How to limit query results by a percentage of the total number of results in MySQL? Excel:数据透视表——从一个值到另一个值的百分比 - Excel: Pivot Table - Percentage of one value from another

赞助商链接