样本数据：
| dateHour |
| -------- |
| 2021-08-01 18:00:00.000 |
| 2021-08-02 20:00:00.000 |
| 2021-08-03 06:00:00.000 |
| 2021-08-04 08:00:00.000 |
| 2021-08-05 09:00:00.000 |
尝试在星期几级别构建一个聚合表。因此，需要从“dateHour”列中提取完整的星期几名称。“dateHour”列包含日期和事件的小时值。它类似于时间戳列。
从“dateHour”列提取完整星期几名称的一种方法是使用以下case when查询。这个解决方案有效，但由于case语句而花费了很多时间。这会影响查询性能。
select
case dayname("dateHour"::date)
            when 'Mon' then 'Monday'
            when 'Tue' then 'Tuesday'
            when 'Wed' then 'Wednesday'
            when 'Thu' then 'Thursday'
            when 'Fri' then 'Friday'
            when 'Sat' then 'Saturday'
            when 'Sun' then 'Sunday'
    end as "day_of_week"
from tableName
我尝试过的另一个查询是：
select TO_CHAR(CURRENT_DATE, 'DYDY') Day_Full_Name;
上面的查询在使用CURRENT_DATE时工作正常，但是当将CURRENT_DATE替换为表中的列名“dateHour”时，它会重复显示星期几的简写。就像这样：
SunSun
MonMon
TueTue
WedWed
ThuThu
要复制DYDY问题，请使用以下代码段进行检查：
with cte as (
select '2021-08-01 18:00:00.000'::timestamp  as "dateHour"
)
select 
    "dateHour"::date as dt,
    TO_CHAR(dt,'DYDY') day_full_name
from cte ;
上面查询的输出：
| DT|DAY_FULL_NAME| 
| -------- | -------- | 
|2021-08-01|Sunday    |
with cte as (
select '2021-08-01 18:00:00.000'::timestamp  as "dateHour"
union all 
select '2021-08-02 20:00:00.000'::timestamp  as "dateHour"
)
select 
    "dateHour"::date as dt,
    TO_CHAR(dt,'DYDY') day_full_name
from cte 
;
输出：
| DT|DAY_FULL_NAME| 
| -------- | -------- | 
|2021-08-01|SunSun    |
|2021-08-02|SunMon    |
预期的输出（在输出中，星期日可以是以下任一项：Sunday / SUNDAY）：
| fullDayofWeekName| 
| -------- | 
| SUNDAY | 
| MONDAY |
| TUESDAY |
| WEDNESDAY |
| THURSDAY |
我需要一种高效的方法来从“dateHour”列生成完整的星期几名称。它不应影响聚合查询的性能。

select 
     TO_CHAR(dt, 'DYDY') as day_name,
     sum(value) as sum_val
from big_table
group by day_name

select 
    convert_to_name_step(dow) as day_name,
    sum_val
from (
    select 
        dayofweek(dt) as dow,
        sum(value) as sum_val
    from big_table
    group by dow
)

with data as (
    select 
        column1 as dt
    from values
    ('2021-08-01 18:00:00.000'::timestamp),
    ('2021-08-02 20:00:00.000'::timestamp),
    (CURRENT_TIMESTAMP)
)
select 
    dt
    ,SYSTEM$TYPEOF(dt) as t
    ,TO_CHAR(dt, 'DYDY') as n
    ,CURRENT_TIMESTAMP
    ,SYSTEM$TYPEOF(CURRENT_TIMESTAMP) as cd_t
    ,TO_CHAR(CURRENT_TIMESTAMP, 'DYDY') as cd_n
from data;

select current_timestamp as ct,
       dayofweek(ct) as dow,
       ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'][dow]::varchar