TSQL将"Other“添加到GROUP by Percentage查询

Question

我如何构造一个查询，将低于某个百分比的任何内容归类为其他？

例如：

Select Country, (COUNT(Country)*100 / (Select COUNT(*) From Logs)) as Perc
FROM Logs
Group by Country
HAVING (COUNT(Country)*100 / (Select COUNT(*) From Logs)) > 5
ORDER BY Perc DESC

我该如何添加一行"Other“，使所有内容的总和小于6%？

Answer 1

另一种方法

;WITH Logs(Country) AS
(
SELECT TOP 10 'UK' FROM sys.objects UNION ALL
SELECT TOP 10 'US' FROM sys.objects UNION ALL
SELECT TOP 1 'Timbuktu' FROM sys.objects
),
GroupedLogs AS
(
SELECT Country, 
      (COUNT(Country) * 100.0 / (SELECT COUNT(*) FROM Logs)) AS Perc
FROM Logs
GROUP BY Country
)
SELECT CASE WHEN Perc<6 THEN 'Other' ELSE Country END AS Country, 
       SUM(Perc) AS Perc
FROM GroupedLogs
GROUP BY CASE WHEN Perc<6 THEN 'Other' ELSE Country END

Answer 2

我认为你需要一个联盟来解决这个问题。未经过测试，但类似于：

SELECT * FROM 
(
    Select 
        Country, 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) as Perc 
    FROM 
        Logs 
    Group by 
        Country 
    HAVING 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) >= 6    

    UNION

    Select 
        'Other' as Country, 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) as Perc 
    FROM 
        Logs 
    HAVING 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) < 6 
)
ORDER BY 
    Perc DESC

Answer 3

或者尝试下面的几行代码。这里的百分比是以CTE计算的：

DECLARE @countries TABLE (name varchar(16), hit INT);


INSERT INTO @countries
VALUES
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('FRA',1),
('FRA',1),
('FRA',1),
('FRA',1),
('FRA',1),
('USA',1),
('MEX',1);

WITH MY_PERCENTAGES AS
(
    SELECT name, 
                CAST(count(*) AS NUMERIC(5,2)) 
                / 
                CAST(SUM(count(*)) OVER() AS NUMERIC(5,2)) * 100.
             AS percentage
    FROM @countries
    GROUP BY name
)
SELECT
    name,
    SUM (percentage) AS percentages
FROM 
(
    SELECT 
        CASE 
        WHEN percentage <6 THEN
            'OTHER'
        ELSE
            name
        END AS name,
        percentage
    FROM MY_PERCENTAGES
) TMP
GROUP BY name;