在bigquery中遇到“case”

dsf9zpds 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(320)

我想为我们的每个订单分配一个值范围，我尝试了以下代码以实现我的目标：

SELECT Subtotal, COUNT(CASE Subtotal)

CASE Subtotal

WHEN Subtotal>0 AND Subtotal<10 THEN ‘0-9,99’
WHEN Subtotal>=10 AND Subtotal<15 THEN ‘10-14,99’
WHEN Subtotal>=15 AND Subtotal<20 THEN ‘15-19,99’
WHEN Subtotal>=20 AND Subtotal<24,90 THEN ‘20-24,89’
WHEN Subtotal>=24,90 AND Subtotal<30 THEN ‘24,90-29,99’
WHEN Subtotal>=30 AND Subtotal<35 THEN ‘30-34,99’
WHEN Subtotal>=35 AND Subtotal<40 THEN ‘35-39,99’
WHEN Subtotal>=40 AND Subtotal<45 THEN ‘40-44,99’
WHEN Subtotal>=45 AND Subtotal<50 THEN ‘45-49,99’
WHEN Subtotal>=50 AND Subtotal<55 THEN ‘50-54,99’
WHEN Subtotal>=55 AND Subtotal<60 THEN ‘55-59,99’
WHEN Subtotal>=60 AND Subtotal<65 THEN ‘60-64,99’
WHEN Subtotal>=65 AND Subtotal<70 THEN ‘65-69,99’
WHEN Subtotal>=70 AND Subtotal<75 THEN ‘70-74,99’
WHEN Subtotal>=75 AND Subtotal<80 THEN ‘75-79,99’
WHEN Subtotal>=80 AND Subtotal<85 THEN ‘80-84,99’
WHEN Subtotal>=85 AND Subtotal<90 THEN ‘85-89,99’
WHEN Subtotal>=90 AND Subtotal<95 THEN ‘90-94,99’
WHEN Subtotal>=95 AND Subtotala<100 THEN ‘95-99,99’
WHEN Subtotal>=100 AND Subtotal<125 THEN ‘100-124,99’
WHEN Subtotal>=125 AND Subtotal<150 THEN ‘125-149,99’
WHEN Subtotal>=150 AND Subtotal<200 THEN ‘150-199,99’
ELSE ‘+200’

END

FROM [orders.orders.shopify.Orders_Shopify_may_2020]

WHERE Subtotal IS NON NULL 

GROUP BY CASE

ORDER BY CASE DESC

但我在bigquery中得到“遇到”“case”“case”“，作为某人的想法？

sql google-bigquery

来源：https://stackoverflow.com/questions/62474243/encountered-case-case-in-bigquery

2条答案

按热度按时间

flvtvl501#

你的陈述应该很像-

SELECT Subtotal, COUNT(CASE Subtotal)

CASE 
WHEN Subtotal>0 AND Subtotal<10 THEN ‘0-9,99’
WHEN Subtotal>=10 AND Subtotal<15 THEN ‘10-14,99’
...
...
ELSE ‘+200’
END
FROM [orders.orders.shopify.Orders_Shopify_may_2020]
WHERE Subtotal IS NON NULL 
GROUP BY CASE
ORDER BY CASE DESC

这种情况在案件陈述中也不起作用-

WHEN Subtotal>=20 AND Subtotal<24,90 THEN ‘20-24,89’
WHEN Subtotal>=24,90 AND Subtotal<30 THEN ‘24,90-29,99’

你必须改变它。

赞(0）回复(0）举报 2021-07-26

b1zrtrql2#

试试下面


# legacySQL

SELECT 
  CASE 
    WHEN Subtotal>0 AND Subtotal<10 THEN '0-9,99'
    WHEN Subtotal>=10 AND Subtotal<15 THEN '10-14,99'
    WHEN Subtotal>=15 AND Subtotal<20 THEN '15-19,99'
    WHEN Subtotal>=20 AND Subtotal<24.90 THEN '20-24,89'
    WHEN Subtotal>=24.90 AND Subtotal<30 THEN '24,90-29,99'
    WHEN Subtotal>=30 AND Subtotal<35 THEN '30-34,99'
    WHEN Subtotal>=35 AND Subtotal<40 THEN '35-39,99'
    WHEN Subtotal>=40 AND Subtotal<45 THEN '40-44,99'
    WHEN Subtotal>=45 AND Subtotal<50 THEN '45-49,99'
    WHEN Subtotal>=50 AND Subtotal<55 THEN '50-54,99'
    WHEN Subtotal>=55 AND Subtotal<60 THEN '55-59,99'
    WHEN Subtotal>=60 AND Subtotal<65 THEN '60-64,99'
    WHEN Subtotal>=65 AND Subtotal<70 THEN '65-69,99'
    WHEN Subtotal>=70 AND Subtotal<75 THEN '70-74,99'
    WHEN Subtotal>=75 AND Subtotal<80 THEN '75-79,99'
    WHEN Subtotal>=80 AND Subtotal<85 THEN '80-84,99'
    WHEN Subtotal>=85 AND Subtotal<90 THEN '85-89,99'
    WHEN Subtotal>=90 AND Subtotal<95 THEN '90-94,99'
    WHEN Subtotal>=95 AND Subtotala<100 THEN '95-99,99'
    WHEN Subtotal>=100 AND Subtotal<125 THEN '100-124,99'
    WHEN Subtotal>=125 AND Subtotal<150 THEN '125-149,99'
    WHEN Subtotal>=150 AND Subtotal<200 THEN '150-199,99'
    ELSE '+200'
  END Subtotal_bucket,
  COUNT(1) volume
FROM [whatever your table reference]
WHERE Subtotal IS NOT NULL 
GROUP BY Subtotal_bucket
ORDER BY volume DESC

另外，我看到您仍在使用bigquery遗留sql—非常推荐迁移到标准sql

赞(0）回复(0）举报 2021-07-26

我来回答

在bigquery中遇到“case”

2条答案

相关问题

热门标签

最新问答