spark.sql("select hour ,count(user id) as users,count(select action from ubh where action=='pv'group by hour order as by hour) as PV,count(select action from ubh where action=='buy'group by hour order as by hour)as BUY from ubh group by hour order as by hour")代码错在哪

这条 Spark SQL 查询语句有一些语法错误：

1. 第一处错误是 `count(user id)`，应该将 `user id` 改为 `user_id`，即 `count(user_id)`。
2. 第二处错误是 `count(select action from ubh where action=='pv'group by hour order as by hour)` 和 `count(select action from ubh where action=='buy'group by hour order as by hour)`，应该将它们的顺序调整一下，即先将 `where` 子句放在 `group by` 子句之后，再将 `order by` 子句放在整个 SQL 语句的末尾。
3. 第三处错误是 `order as by hour`，应该将 `as` 改为 `by`，即 `order by hour`。
4. 最后，整条 SQL 语句的结尾应该是 `order by hour`，而不是 `order as by hour`。

因此，正确的 Spark SQL 查询语句应该是：

spark.sql("SELECT hour, COUNT(user_id) AS users, COUNT(CASE WHEN action = 'pv' THEN 1 END) AS PV, COUNT(CASE WHEN action = 'buy' THEN 1 END) AS BUY FROM ubh GROUP BY hour ORDER BY hour")