在每个垃圾箱里计数
在每个垃圾箱里计数
提问于 2015-03-27 01:15:55
有办法简化这个SQL查询吗？
SELECT
count(case when R1 >= 0 AND R1 < 5 AND (M1/102) >= 0 AND (M1/102) < 4 THEN 1 END) AS BIN0_1,
count(case when R1 >= 0 AND R1 < 5 AND (M1/102) >= 4 AND (M1/102) < 8 THEN 1 END) AS BIN0_2,
count(case when R1 >= 0 AND R1 < 5 AND (M1/102) >= 8 AND (M1/102) < 12 THEN 1 END) AS BIN0_3,
count(case when R1 >= 0 AND R1 < 5 AND (M1/102) >= 12 AND (M1/102) < 16 THEN 1 END) AS BIN0_4,
count(case when R1 >= 0 AND R1 < 5 AND (M1/102) >= 16 AND (M1/102) < 245 THEN 1 END) AS BIN0_5,
count(case when R1 >= 5 AND R1 < 15 AND (M1/102) >= 0 AND (M1/102) < 4 THEN 1 END) AS BIN5_1,
count(case when R1 >= 5 AND R1 < 15 AND (M1/102) >= 4 AND (M1/102) < 8 THEN 1 END) AS BIN5_2,
count(case when R1 >= 5 AND R1 < 15 AND (M1/102) >= 8 AND (M1/102) < 12 THEN 1 END) AS BIN5_3,
count(case when R1 >= 5 AND R1 < 15 AND (M1/102) >= 12 AND (M1/102) < 16 THEN 1 END) AS BIN5_4,
count(case when R1 >= 5 AND R1 < 15 AND (M1/102) >= 16 AND (M1/102) < 20 THEN 1 END) AS BIN5_5,
count(case when R1 >= 5 AND R1 < 15 AND (M1/102) >= 20 AND (M1/102) < 245 THEN 1 END) AS BIN5_6,
count(case when R1 >= 15 AND R1 < 30 AND (M1/102) >= 0 AND (M1/102) < 4 THEN 1 END) AS BIN15_1,
count(case when R1 >= 15 AND R1 < 30 AND (M1/102) >= 4 AND (M1/102) < 8 THEN 1 END) AS BIN15_2,
count(case when R1 >= 15 AND R1 < 30 AND (M1/102) >= 8 AND (M1/102) < 12 THEN 1 END) AS BIN15_3,
count(case when R1 >= 15 AND R1 < 30 AND (M1/102) >= 12 AND (M1/102) < 16 THEN 1 END) AS BIN15_4,
count(case when R1 >= 15 AND R1 < 30 AND (M1/102) >= 16 AND (M1/102) < 20 THEN 1 END) AS BIN15_5,
count(case when R1 >= 15 AND R1 < 30 AND (M1/102) >= 20 AND (M1/102) < 245 THEN 1 END) AS BIN15_6
FROM MyTable
WHERE U1 = 'pmd'
AND day >= '2014-01-01'该表有：
- 列R1的200 k行和100列(数值介于0-600之间)
- 列M1 (数值介于1-25000之间)
结果应该是每个回收站中的计数(出现)行(或列)。
例如，在以下情况下，BIN0_1应该有#出现的情况：
0<R1<5和0 < M1/102 < 4.
上面的查询可以工作，但是由于代码中有更多类似的行(R1在30到45、45和60、60和120等之间)，所以想知道是否有更简单的方法。
提前谢谢。
回答 1
Stack Overflow用户
发布于 2015-03-27 05:31:52
您可以用标志替换比较。这样你就不用重复了：
SELECT (r1_0_5 * m1_0_4) as bin_01
FROM (SELECT (case when R1 >= 0 AND R1 < 5 then 1 else 0 end) as r1_0_5,
(case when R1 >= 5 AND R1 < 15 then 1 else 0 end) as r1_5_15,
(case when R1 >= 15 AND R1 < 30 then 1 else 0 end) as r1_15_30,
(case when (M1/102) >= 0 AND (M1/102) < 4 then 1 else 0 end) as m1_0_4,
. . .
FROM MyTable
WHERE U1 = 'pmd' AND day >= '2014-01-01'
) t;或者，可以将结果放在输出中的不同行上：
SELECT r_bin, m_bin, count(*)
FROM (SELECT (case when R1 >= 0 AND R1 < 5 then '0_5',
when R1 >= 5 AND R1 < 15 then '5_15',
when R1 >= 15 AND R1 < 30 then '15_30'
end) as r_bin,
(case when (M1/102) >= 0 AND (M1/102) < 4 then '0_4'
. . .
end) as m_bin
FROM MyTable
WHERE U1 = 'pmd' AND day >= '2014-01-01'
) t
group by r_bin, m_bin;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29291722
复制相关文章
相似问题
腾讯云开发者
