SQL Increment column value in select statement

问题:

I'm trying to write a Select statement that increments a column value by 50, but the range can end up being 200,000 so I can't do it all in a case statement manually.

Something similar to this, but instead of manually writing the increments

Select count(order_id) as order_count, 
   case when revenue between 0 and 50 then ‘$50’
        when order_value between 51 and 100 then ‘$100’
             else ‘over $101’
   end as revenue_bucket
from Orders
group by 2

回答1:

Turn your revenue into the bucket value, then make string out of it:

SELECT count(order_id) AS order_count, 
        '$' || ((((revenue - 0.01)/50)::int + 1) * 50)::text AS revenue_bucket
FROM Orders
GROUP BY 2;

This obviously runs well past $200,000.

回答2:

You can work with modulo to get this. Limit would be 101 in your example. All you have to do, is cast the result in a string and add the $ before it

Select count(order_id) as order_count, 
    case when revenue < limit then revenue - (revenue % 50) + 50
             else ‘over $101’
  end as revenue_bucket
from Orders
group by 2

回答3:

You can round to the nearest 50 with div (integer division):

revenue div 50 * 50

To round up instead of down:

(revenue div 50 + 1) * 50

To include 50 in the next bracket (so 50 as $50 instead of $100):

((revenue-1) div 50 + 1) * 50

Example query:

select  revenue
,       concat('$', ((revenue-1) div 50 + 1) * 50) as revenue_bucket
from    YourTable

See it working at SQL Fiddle.

回答4:

figured out something similar

select floor((revenue+49)/50)*50 as revenue_bucket, 
       count(1) as order_count
from Orders 
group by 1;