How to bucket a SQL server query

问题:

I have the following table in SQL Server:

Date            Browser         Country         Time(ms)
----------------------------------------------------------
2019-05-06      Chrome          US              1000
2019-05-06      Chrome          US              560
2019-05-07      Firefox         JP              2300
2019-05-07      Edge            US              1200
2019-05-07      Chrome          JP              3000
2019-05-07      Chrome          JP              3200
2019-05-07      Chrome          JP              2100
2019-05-07      Firefox         US              2200

I need to "bucketize" the results based on the time it took to complete. The end result would be something like this:

Time(ms)            US              JP
--------------------------------------
0-1s                2               0
1-2s                1               0
2-3s                1               2
3+s                 0               2

The closest thing I was able is to do is a query like this:

 SELECT 
  [Country],
  COUNT(CASE WHEN [Time] >= 0 AND [Time] < 1000 THEN 1 END) AS '0 - 1s',
  COUNT(CASE WHEN [Time] >= 1000 AND [Time] < 2000 THEN 1 END) AS '1 - 2s',
  COUNT(CASE WHEN [Time] >= 2000 AND [Time] < 3000 THEN 1 END) AS '2 - 3s',
  COUNT(CASE WHEN [Time] >= 3000 THEN 1 END) AS '+3s'
  FROM [dbo].[MyTable]
  GROUP BY [Country]

But this schema is not quite what I am looking for since my result is:

Country         0 - 1s      1 - 2s       2 - 3s     +3s
---------------------------------------------------------
US              2           1           1           0
JA              0           0           2           2

How should I approach this problem?

回答1:

With conditional aggregation you already were on the right track for one possible solution. But have the condition on the country. In a WHERE filter for the durations and stick the different durations together with UNION ALL.

SELECT '0 - 1s' [Time(ms)],
       count(CASE
               WHEN [Country] = 'US' THEN
                 1
             END) [US],
       count(CASE
               WHEN [Country] = 'JP' THEN
                 1
             END) [JP]
       FROM [dbo].[mytable]
       WHERE [Time] >= 0
             AND [Time] < 1000
UNION ALL
...
UNION ALL
SELECT '+3s' [Time(ms)],
       count(CASE
               WHEN [Country] = 'US' THEN
                 1
             END) [US],
       count(CASE
               WHEN [Country] = 'JP' THEN
                 1
             END) [JP]
       FROM [dbo].[mytable]
       WHERE [Time] >= 3000;

回答2:

The following will give you a single list which includes the country i.e. so it doesn't make each country a column. To make each a column you either have to do what Sticky-Bit has done, or you have to pivot - either way you have to handle each country individually (unless you build the pivot using dynamic SQL). But maybe you end user can use this list instead of needing a separate column?

declare @MyTable table ([Date] date, Browser varchar(32), Country varchar(2), [Time] int)

insert into @MyTable ([Date], Browser, Country, [Time])
  select '2019-05-06',      'Chrome',          'US',              1000
  union all select '2019-05-06',      'Chrome',          'US',              560
  union all select '2019-05-07',      'Firefox',         'JP',              2300
  union all select '2019-05-07',      'Edge',            'US',              1200
  union all select '2019-05-07',      'Chrome',          'JP',              3000
  union all select '2019-05-07',      'Chrome',          'JP',              3200
  union all select '2019-05-07',      'Chrome',          'JP',              2100
  union all select '2019-05-07',      'Firefox',         'US',              2200

select Duration, Country, count(*)
from (
  select *
    , CASE WHEN [Time] >= 0 AND [Time] < 1000 THEN '0 - 1s'
    WHEN [Time] >= 1000 AND [Time] < 2000 THEN '1 - 2s'
    WHEN [Time] >= 2000 AND [Time] < 3000 THEN '2 - 3s'
    ELSE '+3s' END Duration
    , CASE WHEN [Time] >= 0 AND [Time] < 1000 THEN 0
    WHEN [Time] >= 1000 AND [Time] < 2000 THEN 1
    WHEN [Time] >= 2000 AND [Time] < 3000 THEN 2
    ELSE 3 END DurationOrder
  from @MyTable
) X
group by Duration, DurationOrder, Country
order by DurationOrder, Country