我正在尝试执行一个简单的查询(ORACLE DB),以显示两个日期列(START_TIME和END_TIME)以及总数以及百分比,并按月份进行过滤,然后按年份过滤db中两个日期字段中定义的数据类型作为日期,我想以以下格式显示数据
样本数据无时间戳
start_time - 01.12.2020
end_time - 02.12.2020
询问
SELECT
to_char(cast(START_TIME as date),'YYYY') as START_BY_YEAR ,
to_char(cast(END_TIME as date),'YYYY') as END_BY_YEAR ,
to_char(cast(START_TIME as date),'MM.YYYY')as START_BY_MONTH,
to_char(cast(END_TIME as date),'MM.YYYY') as END_BY_MONTH,
to_char(cast(START_TIME as date),'DD.MM.YYYY') as START_BY_DAY,
to_char(cast(END_TIME as date),'DD.MM.YYYY') as END_BY_DAY,
COUNT(*) as count,
round(100*ratio_to_report(count(*)) over (), 4) percentage
FROM SCHEMA_NAME.TABLE_NAME
WHERE to_char(cast(START_TIME as date),'MM.YYYY')='12.2020'
and to_char(cast(END_TIME as date),'MM.YYYY')='12.2020'
GROUP BY
START_BY_YEAR,
END_BY_YEAR,
START_BY_MONTH,
END_BY_MONTH,
START_BY_DAY,
END_BY_DAY
order by
START_BY_YEAR desc,
END_BY_YEAR desc,
START_BY_MONTH desc,
END_BY_MONTH desc,
START_BY_DAY desc,
END_BY_DAY desc
我收到错误消息
ORA-00904: "END_BY_DAY": invalid identifier
我究竟在哪里得到错误尚不清楚
预期结果集
START_BY_YEAR END_BY_YEAR START_BY_MONTH END_BY_MONTH START_BY_DAY END_BY_DAY
2012 2012 12.2012 12.2012 01.12.2020 02.12.2020
请注意,为了方便阅读,我已经扩展了代码。
建议 ?
要解决该错误,必须从GROUP BY
子句中删除别名。另外,如果表中有date
列,则不需要CASTs
,查询可以重新编写为:
SELECT
to_char(START_TIME,'YYYY') START_BY_YEAR ,
to_char(END_TIME,'YYYY') END_BY_YEAR ,
to_char(START_TIME,'MM.YYYY') START_BY_MONTH,
to_char(END_TIME,'MM.YYYY') END_BY_MONTH,
to_char(START_TIME,'DD.MM.YYYY') START_BY_DAY,
to_char(END_TIME,'DD.MM.YYYY') END_BY_DAY,
COUNT(*) as count,
round(100*ratio_to_report(count(*)) over (), 4) percentage
FROM TABLE_NAME
WHERE to_char(START_TIME,'MM.YYYY')='12.2020'
and to_char(END_TIME,'MM.YYYY')='12.2020'
GROUP BY
to_char(START_TIME,'YYYY') ,
to_char(END_TIME,'YYYY') ,
to_char(START_TIME,'MM.YYYY') ,
to_char(END_TIME,'MM.YYYY') ,
to_char(START_TIME,'DD.MM.YYYY') ,
to_char(END_TIME,'DD.MM.YYYY')
order by
START_BY_YEAR desc,
END_BY_YEAR desc,
START_BY_MONTH desc,
END_BY_MONTH desc,
START_BY_DAY desc,
END_BY_DAY desc
用这样的表:
create table table_name as
(
select date '2020-12-01' start_time,
date '2020-12-02' end_time
from dual
);
该查询给出:
START_BY_YEA END_BY_YEAR START_BY_MON END_BY_MONTH START_BY_DAY END_BY_DAY COUNT PERCENTAGE
------------ ------------ ------------ ------------ ------------ ------------ ----- ----------
2020 2020 12.2020 12.2020 01.12.2020 02.12.2020 1 100
1 row selected.
我上面已经提到过,数据类型为date,您尝试重新运行,但是我再次遇到错误ORA-00904:“ END_BY_DAY”:无效标识符00904。00000-“%s:无效标识符” *原因:*操作:行错误:19列:1
刚刚编辑以显示完整的工作查询,简化了
非常感谢它的工作,我对错误的理解