时间戳

首页 > 解决方案 > AWS Redshift 中的列不存在或架构不存在错误

sql - AWS Redshift 中的列不存在或架构不存在错误

问题描述

工作查询需要更改以包含另一个表中的列。在 LEFT JOINing 时，我收到“列不存在”错误。在 ON 语句中使用列而不是列别名，第一个错误会变成“架构不存在”错误。

这会产生“列“a.audience_id”不存在错误”

        select
            criteria,
            trim(split_part(criteria, ':', 3))  as audience_id,
            t.category as audience_name,
            row_number() OVER (PARTITION BY trim(split_part(criteria, ':', 3)) ORDER BY date desc) as rownumber
        from
            client.adwords_insights_audience a
            left join core.googleads_inmarketaudience_xref x
            on a.audience_id = x.criterion_id
        group by
        criteria,
           trim(split_part(criteria, ':', 3)),
            t.category

并且将 ON 子句更改为以下会产生“模式“a”不存在”错误

        select
            criteria,
            trim(split_part(criteria, ':', 3))  as audience_id,
            t.category as audience_name,
            row_number() OVER (PARTITION BY trim(split_part(criteria, ':', 3)) ORDER BY date desc) as rownumber
        from
            client.adwords_insights_audience a
            left join core.googleads_inmarketaudience_xref x
            on a.trim(split_part(criteria, ':', 3)) = x.criterion_id
        group by
        criteria,
           trim(split_part(criteria, ':', 3)),
            t.category

原始查询（如下）完美运行。只有当我离开时才会出现错误。

        select
            criteria,
            trim(split_part(criteria, ':', 3))  as audience_id,
            row_number() OVER (PARTITION BY trim(split_part(criteria, ':', 3)) ORDER BY date desc) as rownumber
        from
            client.adwords_insights_audience a
        group by
        criteria,
           trim(split_part(criteria, ':', 3))

“日期”栏也可以，为了清楚起见，我将其与其他几个帖子一起从这篇文章中删除。我希望最终输出中的 t.category 列。

标签： sqlamazon-redshift

解决方案

我怀疑如果您修复表别名，您的查询可能会起作用。以下是我对列的来源的最佳猜测：

select x.criteria,
       trim(split_part(x.criteria, ':', 3))  as audience_id,
       a.category as audience_name,
       row_number() over (partition by trim(split_part(x.criteria, ':', 3)) order by  ?.date desc) as rownumber
from client.adwords_insights_audience a left join 
     core.googleads_inmarketaudience_xref x
     on a.audience_id = x.criterion_id
group by x.criteria,
         trim(split_part(x.criteria, ':', 3)),
         a.category;

那?是因为我不知道date从哪里来。

推荐阅读