向 Redshift 查询时出错 [英] Error in query to Redshift

问题描述

我需要计算每个月的用户数量.我已经写了查询.

I need to count the number of users in every month. I have written query.

SELECT COUNT(distinct user_id) 
from (
select TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') as MyMonth
from generate_series(1,60,1) AS s(a)
) months 
 LEFT OUTER JOIN database.users
ON months.MyMonth=to_char(created_at, 'YYYY-MM')
GROUP BY months.MyMonth

但是当我尝试执行它时，错误出现:Redshift 表不支持指定的类型或函数(每个 INFO 消息一个)."是否可以以某种方式重写此查询或请指出错误在哪里?

But when I try to execute it, the mistake appears: "Specified types or functions (one per INFO message) not supported on Redshift tables." Whether it is possible to rewrite somehow this query or specify please where a mistake?

推荐答案

Amazon Redshift 不完全支持 generate_series() 命令.

The generate_series() command is not fully supported on Amazon Redshift.

它可以用于仅在Leader节点上运行的查询，但不能用于涉及表的查询，因为它们涉及集群节点.唯一的解决方法是创建一个包含大量值的大表并加入它以获得一系列数字.(前期做了一些工作，但之后效果很好.)

It can be used for queries that run solely on the Leader node, but cannot be used in queries that involve tables because they involve cluster nodes. The only workaround is to create a large table with lots of values and join to it to get a range of numbers. (A bit of work up front, but then it works fine.)

您会在 StackOverflow 上找到许多与 generate_series() 相关的类似问题.

You'll find many similar Questions on StackOverflow related to generate_series().

这篇关于向 Redshift 查询时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！