代码之家 › 专栏 › 技术社区 › jrussin

SQL加法公式

sql-server sql

jrussin · 技术社区 · 7 年前

我有一个示例表,如下所示。

我试图在SQL中创建一个列,显示每个客户每年拥有大小为S的百分比。

因此,输出应该类似于: (更正:2019年的客户C百分比应为1)

4 回复 | 直到 7 年前

Tim Mylott 7 年前

窗口功能将使您到达那里。

DECLARE @TestData TABLE
    (
        [Customer] NVARCHAR(2)
      , [CustomerYear] INT
      , [CustomerCount] INT
      , [CustomerSize] NVARCHAR(2)
    );

INSERT INTO @TestData (
                          [Customer]
                        , [CustomerYear]
                        , [CustomerCount]
                        , [CustomerSize]
                      )
VALUES ( 'A', 2017, 1, 'S' )
     , ( 'A', 2017, 1, 'S' )
     , ( 'B', 2017, 1, 'S' )
     , ( 'B', 2017, 1, 'S' )
     , ( 'B', 2018, 1, 'S' )
     , ( 'A', 2018, 1, 'S' )
     , ( 'C', 2017, 1, 'S' )
     , ( 'C', 2019, 1, 'S' );

SELECT   DISTINCT [Customer]
                , [CustomerYear]
                , SUM([CustomerCount]) OVER ( PARTITION BY [Customer]
                                                 , [CustomerYear]
                                    ) AS [CustomerCount]
                , SUM([CustomerCount]) OVER ( PARTITION BY [CustomerYear] ) AS [TotalCount]
                , SUM([CustomerCount]) OVER ( PARTITION BY [Customer]
                                                 , [CustomerYear]
                                    ) * 1.0 / SUM([CustomerCount]) OVER ( PARTITION BY [CustomerYear] ) AS [CustomerPercentage]
FROM     @TestData
ORDER BY [CustomerYear]
       , [Customer];

我会给你

Customer CustomerYear CustomerCount TotalCount  CustomerPercentage
-------- ------------ ------------- ----------- ---------------------------------------
A        2017         2             5           0.400000000000
B        2017         2             5           0.400000000000
C        2017         1             5           0.200000000000
A        2018         1             2           0.500000000000
B        2018         1             2           0.500000000000
C        2019         1             1           1.000000000000

Tab Alleman 7 年前

假设一年内没有客户的重复行,您可以使用窗口功能:

select t.*,
       sum(count) over (partition by year) as year_cnt,
       count * 1.0 / sum(count) over (partition by year) as ratio
from t;

Kevin 7 年前

select
    customer,
    year
from @tmp
where size = 'S'
group by customer, year

... 这将为每个客户/年度组合的“S”条目获取一行。

接下来,我想要该客户/年度组合的总计数:

select
    customer,
    year,
    SUM(itemCount) as customerItemCount
from @tmp
where size = 'S'
group by customer, year

... 现在,我们如何计算出 全部的 特定年份的客户?我们需要一个子查询——并且我们需要该子查询引用主查询中的年份。

select
    customer,
    year,
    SUM(itemCount) as customerItemCount,
    (select SUM(itemCount) from @tmp t2 where year=t.year) as FullTotalForYear
from @tmp t
where size = 'S'
GROUP BY customer, year

select
    customer,
    year,
    cast(SUM(itemCount) as float) / 
        (select SUM(itemCount) from @tmp t2 where year=t.year)
        as PercentageOfYear
from @tmp t
where size = 'S'
GROUP BY customer, year

有道理?

forpas 7 年前

通过两个组的联接:
第一个 尺寸、年份、客户 和
第二个 .

select 
  t.customer, t.year, t.count, t.size, 
  ty.total_count, 1.0 * t.count / ty.total_count percentage 
from (
  select t.customer, t.year, sum(t.count) count, t.size
  from tablename t
  group by t.size, t.year, t.customer
) t inner join (
  select t.year, sum(t.count) total_count, t.size
  from tablename t
  group by t.size, t.year
) ty
on ty.size = t.size and ty.year = t.year
order by t.size, t.year, t.customer;

见 demo