首页 文章

在SQL Google BigQuery中计算CASE

提问于
浏览 次
0
SELECT
SUM(CASE WHEN TipPercentage < 0 THEN 1 ELSE 0 END) AS 'No Tip'
SUM(CASE WHEN TipPercentage BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'Less but still a Tip'
SUM(CASE WHEN TipPercentage BETWEEN 5 AND 10 THEN 1 ELSE 0 END) AS 'Decent Tip'
SUM(CASE WHEN TipPercentage > 10 THEN 1 ELSE 0 END) AS 'Good Tip'
SUM(ELSE ) AS 'Something different'
END AS TipRange,
TipPercentage,
Tipbin)
FROM
(SELECT
case when tip_amount=0 then 'No Tip'
when (tip_amount > 0 and tip_amount <=5) then '0-5'
when (tip_amount > 5 and tip_amount <=10) then '5-10'
when (tip_amount > 10 and tip_amount <=20) then '10-20'
when tip_amount > 20 then '> 20'
else 'other'
end as Tipbin,
SUM(tip_amount) as Tips,
ROUND(avg((tip_amount)/(total_amount-tip_amount))*100,3) as TipPercentage
FROM `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE trip_distance >0
AND fare_amount/trip_distance BETWEEN 2 AND 10
AND dropoff_datetime > pickup_datetime
group by 1,2,3,tip_amount,tipbin)

我试图从谷歌Bigquery获取数据，其中每个'无提示'，'少但仍然是提示'，'体面提示'，'好提示'和'某些不同'的总和将根据每个的计数返回 . 但是，我收到一个语法错误，说字符串'No Tip'是意外的 .

有人可以指导我吗？

谢谢！

编辑：

我使用标准SQL得到的错误代码是

Error: Syntax error: Unexpected string literal 'No Tip' at [2:55]

当我尝试使用Legacy SQL运行时，我得到了这个：

Error: Encountered " "AS" "AS "" at line 2, column 52. Was expecting: <EOF>
sql google-bigquery

2 回答

  • 1

    根据您的描述，您似乎基本上想要您的子查询 . 在这里它被清理了一点，修复了语法错误：

    SELECT (case when tip_amount = 0 then 'No Tip'
             when tip_amount > 0 and tip_amount <= 5 then '0-5'
             when tip_amount > 5 and tip_amount <= 10 then '5-10'
             when tip_amount > 10 and tip_amount <= 20 then '10-20'
             when tip_amount > 20 then '> 20'
             else 'other'
        end) as Tipbin,
       COUNT(*) as num,
       SUM(tip_amount) as Tips,
       ROUND(avg((tip_amount)/(total_amount-tip_amount))*100,3) as TipPercentage
FROM `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE trip_distance > 0 AND
      fare_amount/trip_distance BETWEEN 2 AND 10 AND
      dropoff_datetime > pickup_datetime
GROUP BY TIpBin
ORDER BY MIN(tip_amount);
    回复于
  • 0

    在您需要的每笔金额之后，您丢失了一堆逗号：

    SELECT
SUM(CASE WHEN TipPercentage < 0 THEN 1 ELSE 0 END) AS 'No Tip',
SUM(CASE WHEN TipPercentage BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'Less but still a Tip',
SUM(CASE WHEN TipPercentage BETWEEN 5 AND 10 THEN 1 ELSE 0 END) AS 'Decent Tip',
SUM(CASE WHEN TipPercentage > 10 THEN 1 ELSE 0 END) AS 'Good Tip',
-- SUM(ELSE ) AS 'Something different'//this line is missing something in 
-- the sum function
END AS TipRange,
TipPercentage,
Tipbin
FROM
(SELECT
case when tip_amount=0 then 'No Tip'
when (tip_amount > 0 and tip_amount <=5) then '0-5'
when (tip_amount > 5 and tip_amount <=10) then '5-10'
when (tip_amount > 10 and tip_amount <=20) then '10-20'
when tip_amount > 20 then '> 20'
else 'other'
end as Tipbin,
SUM(tip_amount) as Tips,
ROUND(avg((tip_amount)/(total_amount-tip_amount))*100,3) as TipPercentage
FROM `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE trip_distance >0
AND fare_amount/trip_distance BETWEEN 2 AND 10
AND dropoff_datetime > pickup_datetime
group by 1,2,3,tip_amount,tipbin) T --All derived tables must have an alias
    回复于

相关问题