SQL查询以选择占总数的百分比

Question

我有一个MSSQL表存储，该存储在表中具有以下列：

Storeid, NumEmployees
1       125 
2       154
3       10 
4       698
5       54  
6       98
7       87
8       100
9       58
10      897

有人可以帮我进行SQL查询，以产生占雇员总数（NumEmployees）30％的顶级商店（storeID）吗？

Answer 1

WITH cte
AS (SELECT storeid,
numemployees,
( numemployees * 100 ) / SUM(numemployees) OVER (PARTITION BY 1)
AS
percentofstores
FROM stores)
SELECT *
FROM cte
WHERE percentofstores >= 30
ORDER BY numemployees desc

工作演示

不使用SUM / OVER的替代方法

SELECT s.storeid, s.numemployees 
FROM   (SELECT SUM(numemployees) AS [tots] 
        FROM   stores) AS t, 
       stores s 
WHERE  CAST(numemployees AS DECIMAL(15, 5)) / tots >= .3 
ORDER BY s.numemployees desc

工作演示

请注意，在第二个版本中，我决定不除以100。这需要强制转换为十进制，否则将隐式转换为int，导致不返回任何记录

同样，我也不太清楚您是否需要这样做，但是您可以将其添加TOP 1到两个查询中，并将结果限制为商店数量最多的商店，即超过30％的商店

更新

根据您的评论，听起来可以解释凯文（Kevin）

您需要从员工人数最多的商店开始，一直到直到您拥有至少30％的行

这很困难，因为它需要一个运行百分比，并且它有一个装箱问题，但这确实起作用。请注意，我还包括了其他两个测试用例（百分比完全相等，并且刚好在前两个合计的百分比之内）

工作演示

DECLARE @percent DECIMAL (20, 16)

SET @percent = 0.3
--Other test values
--SET @percent = 0.6992547128452433
--SET @percent = 0.6992547128452434

;WITH sums 
     AS (SELECT DISTINCT s.storeid, 
                         s.numemployees, 
                         s.numemployees + Coalesce(SUM(s2.numemployees) OVER ( 
                                                   PARTITION 
                                                   BY 
                                                   s.numemployees), 0) 
                         runningsum 
         FROM   stores s 
                LEFT JOIN stores s2 
                  ON s.numemployees < s2.numemployees), 
     percents 
     AS (SELECT storeid, 
                numemployees, 
                runningsum, 
                CAST(runningsum AS DECIMAL(15, 5)) / tots.total 
                running_percent, 
                Row_number() OVER (ORDER BY runningsum, storeid ) rn 
         FROM   sums, 
                (SELECT SUM(numemployees) total 
                 FROM   stores) AS tots) 
SELECT p.storeID,
       p.numemployees,
       p.running_percent,
       p.running_percent,
       p.rn 
FROM   percents p 
       CROSS JOIN (SELECT MAX(rn) rn 
                  FROM   percents 
                  WHERE  running_percent = @percent) exactpercent

       LEFT JOIN (SELECT MAX(rn) rn 
                   FROM   percents 
                   WHERE  running_percent <= @percent) underpercent 
         ON p.rn <= underpercent.rn 
             OR ( exactpercent.rn IS NULL 
                  AND p.rn <= underpercent.rn + 1 ) 
WHERE 
      underpercent.rn is not null or p.rn = 1