根据数据创建范围 [英] Create ranges based on the data

问题描述

我有以不同价格出售的产品.我想查看在特定价格范围内售出了多少产品.为此，我需要遍历数据，看看如何划分范围，然后获得该范围内的产品计数.

I have products sold at different prices. I want to see how many products were sold in a particular price range. For this, I need to go through the data and see how to divide the ranges and then get the count of products in that range.

数据如下所示-

Product        Price sold
   A              4.5
   B              45.7
   C              20
   D              20.1
   E              36.8
   F              50

例如，对于以上数据，我看到最小值为4.5，最大值为50.因此，我决定将价格范围划分为-0-10 $，11-20 $，21-30 $，30-40 $，40-50 $

For example, for the above data I see that the min is 4.5 and max is 50. So, I decide to divide the price range like- 0-10$, 11-20$, 21-30$, 30-40$, 40-50$

因此，结果应类似于-

Range           No. of products sold
0-10                    1
11-20                   2
21-30                   0
30-40                   1
40-50                   2

价格为浮动价格，因此，范围应考虑浮动值.这可能吗?

The prices are in float so, the ranges should take care of the float values. Is this possible?

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
WITH price_ranges AS (
  SELECT '0-10' price_range UNION ALL 
  SELECT '11-20' UNION ALL 
  SELECT '21-30' UNION ALL 
  SELECT '30-40' UNION ALL 
  SELECT '40-50' 
)
SELECT price_range, COUNT(1) number_sold
FROM `project.dataset.table`
JOIN price_ranges 
ON CAST(price_sold AS INT64)
  BETWEEN CAST(SPLIT(price_range, '-')[OFFSET(0)] AS INT64) 
  AND CAST(SPLIT(price_range, '-')[OFFSET(1)] AS INT64)
GROUP BY price_range
-- ORDER BY price_range

如果要应用于您的问题的样本数据-结果为

If to apply to sample data from your question - result is

Row price_range number_sold  
1   0-10        1    
2   11-20       2    
3   30-40       1    
4   40-50       2    

这篇关于根据数据创建范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！