Sql -按范围分组(m2)，并将范围内的元素的价格相加。

Question

我是sql新手，我不知道如何处理这个查询。

我有这张桌子m2AndPrices。它包含资产及其相关的表面和价值。

|---------------------------------------------|
|id     |     code      |   m2   |      value |
|---------------------------------------------|
|32980  | 143452asd10   | 11.17  |      0     |
|---------------------------------------------|
|25107  | 20152fgg219   | 57.00  |   55304,99 |
|---------------------------------------------|
|5692   | 203hhh75735   | 87.18  |   98334,16 |
|---------------------------------------------|
|31500  | 200788fgfg193 | 240.64 |  164401,75 |
|---------------------------------------------|

按平方米(M2)分组并进行计数，我得到如下输出：

|----------------------------------|
|m2section |     number_of_assets  |
|----------------------------------|
|<1        |         175           |
|----------------------------------|
|1-5       |         286           |
|----------------------------------|
|5-10      |         374           |
|----------------------------------|
|10-20     |         573           |  
|----------------------------------|
|20-40     |        5212           |
|----------------------------------|
|40-80     |        3892           |
|----------------------------------|
|80-120    |        4121           |
|----------------------------------|
|120-180   |        1849           |
|----------------------------------|
|180-250   |         705           |
|----------------------------------|
|250-500   |         430           |
|----------------------------------|
| >500     |         131           |
|----------------------------------|

守则：

    SELECT * FROM (
    SELECT cast(m2section as varchar), COUNT(*) as number_of_assets FROM (
    SELECT 
    CASE 
        WHEN m2 <= 1 THEN '<1'
        WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
        WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
        WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
        WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
        WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
        WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
        WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
        WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
        WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
        WHEN m2 > 500 THEN '>500' END AS m2section  
FROM (select * from m2AndPrices x) as a
) as A 
GROUP BY m2section) as B
order by CASE
    WHEN m2section = '<1' THEN 1
    WHEN m2section = '1-5' THEN 2
    WHEN m2section = '5-10'  THEN 3 
    WHEN m2section = '10-20' THEN 4
    WHEN m2section = '20-40' THEN 5
    WHEN m2section = '40-80' THEN 6
    WHEN m2section = '80-120' THEN 7
    WHEN m2section = '120-180' THEN 8
    WHEN m2section = '180-250' THEN 9
    WHEN m2section = '250-500' THEN 10
    WHEN m2section = '>500' THEN 11
END
;

如果没有很好的缩进，很抱歉。

我需要另一个列来计算每个范围中元素数量的值，如下所示：

|------------------------------------------------------|
|m2section |     number_of_assets  |  total value ($)
|------------------------------------------------------|
|<1        |         175           |    345.436
|------------------------------------------------------|
|1-5       |         286           |    864.364
|------------------------------------------------------|
|5-10      |         374           |   364.334
|------------------------------------------------------|
|10-20     |         573           |  1.364.364 
|------------------------------------------------------|
|20-40     |        5212           |  8.364.364
|------------------------------------------------------|
|40-80     |        3892           |  6.364.364
|------------------------------------------------------|
|80-120    |        4121           |     .....
|------------------------------------------------------|
|120-180   |        1849           |     .....
|------------------------------------------------------|
|180-250   |         705           |     .....
|------------------------------------------------------|
|250-500   |         430           |     .....
|------------------------------------------------------|
| >500     |         131           |     .....
|------------------------------------------------------|

但我不知道在哪里能计算出价值($)。

任何想法都会被接受。

非常感谢

Answer 1

在Postgres，我建议：

SELECT (CASE WHEN m2 <= 1 THEN '<1'
             WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
             WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
             WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
             WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
             WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
             WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
             WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
             WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
             WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
             WHEN m2 > 500 THEN '>500'
        END) AS m2section,
       COUNT(*) as num_assets,
       SUM(value) as sum_value 
FROM m2AndPrices mp
GROUP BY m2section
ORDER BY MIN(m2);

现在，我还要简化CASE表达式：

SELECT (CASE WHEN m2 <= 1 THEN '<1'
             WHEN m2 <= 5 THEN '1-5'
             WHEN m2 <= 10 THEN '5-10' 
             WHEN m2 <= 20 THEN '10-20'
             WHEN m2 <= 40 THEN '20-40'
             WHEN m2 <= 80 THEN '40-80'
             WHEN m2 <= 120 THEN '80-120'
             WHEN m2 <= 180 THEN '120-180'
             WHEN m2 <= 250 THEN '180-250'
             WHEN m2 <= 500 THEN '250-500'
             WHEN m2 > 500 THEN '>500'
        END) AS m2section,
       COUNT(*) as num_assets,
       SUM(value) as sum_value 
FROM m2AndPrices mp
GROUP BY m2section
ORDER BY MIN(m2);

备注：

• CASE表达式是按顺序(保证)计算的，因此不需要AND表达式。
• 可以在GROUP BY中使用列别名。强烈推荐列别名而不是列号，因为后者在最新版本的SQL标准中被废弃。
• ORDER BY有点诡计，但它将根据m2值进行排序，这正是您真正想要的。
• 我假设源表中的每一行都表示一个没有重复项的资产，因此COUNT(*)是合适的。如果您可以有副本，那么您需要COUNT(DISTINCT)，但是DISTINCT会产生开销，所以除非有必要，否则不要使用它。

Answer 2

我认为，如果您只使用一个原始表，您可以在这里大大简化查询吗？M2ANDPrices

首先，你想按m2分类，对吗？

SELECT 
  CASE
    WHEN m2 <= 1 THEN '<1'
    WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
    WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
    WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
    WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
    WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
    WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
    WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
    WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
    WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
    WHEN m2 > 500 THEN '>500' 
  END AS m2section
  FROM m2AndPrices
  GROUP BY 1 -- you can group by giving the index of the columns in the SELECT statement  

这是起点。现在，如果您想要计数行数，则使用COUNT。为了安全起见，最好是进行计数(不同的id)，这样就不会重复计算。在您的例子中，计数(不同的id)来计算表中的销售数量或其他任何内容。

当你想要得到总价值时，你使用和。所以你要做和(Total_value)

完整代码：

SELECT 
  CASE
    WHEN m2 <= 1 THEN '<1'
    WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
    WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
    WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
    WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
    WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
    WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
    WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
    WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
    WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
    WHEN m2 > 500 THEN '>500' 
  END AS m2section,
  COUNT(DISTINCT id) AS number_of_assets_per_m2_band,
  SUM(total_value) AS total_value_per_m2_band
  FROM m2AndPrices
  GROUP BY 1 -- you can group by giving the index of the columns in the SELECT statement 
  ORDER BY 1 DESC;