使用 CASE 和 generate_series() 进行查询，对生成的时间戳进行降序排序

Question

我是新手，并尝试使用以下代码创建一个函数：

CREATE OR REPLACE FUNCTION public.get_bulan()
returns table (request_detail timestamp with time zone)
language plpgsql stable
as $function$
begin
return query
select
case
when (extract(DAY FROM now()) >= 25) then generate_series(date_trunc('year', now()), date_trunc('day', now()) ,interval '1 month')
when (select extract(month FROM now()) = 2) then now() - (interval '1' month * generate_series(0,1))
when (select extract(month FROM now()) = 1) then now() - (interval '1' month * generate_series(0,2))
else generate_series((select date(date_trunc('year', now()))), (select date(now())-'1 month'::interval), interval '1 month')
end
order by timetstamptz(request_detail) desc;
end;
$function$;

上述查询的结果为：

2017-01-01 00:00:00
2017-02-01 00:00:00
2017-03-01 00:00:00
2017-04-01 00:00:00
2017-05-01 00:00:00
2017-06-01 00:00:00
2017-07-01 00:00:00

我尝试使用order by desc和order by timestamp desc但它不起作用。我想通过降序进行排序，所以我得到从 2017-07-01 到 2017-01-01 的结果。我该怎么做？

Answer 1

您有两种方法可以做到这一点：

1. 添加as result_timestamp order by result_timestamp desc;

   CREATE OR REPLACE FUNCTION public.get_bulan()
   returns table (request_detail timestamp with time zone)
   language plpgsql stable
   as $function$
   begin
   return query
   select 
   case
   when (extract(DAY FROM now()) >= 25) then generate_series(date_trunc('year', now()), date_trunc('day', now()) ,interval '1 month')
   when (select extract(month FROM now()) = 2) then now() - (interval '1' month * generate_series(0,1))
   when (select extract(month FROM now()) = 1) then now() - (interval '1' month * generate_series(0,2))
   else generate_series((select date(date_trunc('year', now()))), (select date(now())-'1 month'::interval), interval '1 month')
   end as result_timestamp order by result_timestamp desc;
   end;
   $function$;
   

2. 或者，您可以从过程中删除排序，并在从其他地方调用它时订购：

   CREATE OR REPLACE FUNCTION public.get_bulan()
   returns table (request_detail timestamp with time zone)
   language plpgsql stable
   as $function$
   begin
   return query
   select 
   case
   when (extract(DAY FROM now()) >= 25) then generate_series(date_trunc('year', now()), date_trunc('day', now()) ,interval '1 month')
   when (select extract(month FROM now()) = 2) then now() - (interval '1' month * generate_series(0,1))
   when (select extract(month FROM now()) = 1) then now() - (interval '1' month * generate_series(0,2))
   else generate_series((select date(date_trunc('year', now()))), (select date(now())-'1 month'::interval), interval '1 month')
   end;
   end;
   $function$;
   

并打电话：

select request_detail from public.get_bulan() order by request_detail desc

注意：第二个非常柔韧，您可以轻松操作。

Answer 2

select
(case 
when (extract(DAY FROM now()) >= 25) then generate_series(date_trunc('year', now()), date_trunc('day', now()) ,interval '1 month')
when (select extract(month FROM now()) = 2) then now() - (interval '1' month * generate_series(0,1))
when (select extract(month FROM now()) = 1) then now() - (interval '1' month * generate_series(0,2))
else generate_series((select date(date_trunc('year', now()))), (select date(now())-'1 month'::interval), interval '1 month')
end) as time_st  order by time_st desc

输出：

2017-07-01 00:00:00+05:30
2017-06-01 00:00:00+05:30
2017-05-01 00:00:00+05:30
2017-04-01 00:00:00+05:30
2017-03-01 00:00:00+05:30
2017-02-01 00:00:00+05:30
2017-01-01 00:00:00+05:30

Answer 3

SQLCASE语句的第一个和最后一个分支按降序返回行，其他两个按升序返回行。因此，您需要在外部SELECT中再ORDER BY。

干净的解决方法是使所有 4 个分支以相同的顺序返回行。

或者更确切地说，完全重写它以解开混乱：

SELECT *
FROM   generate_series(
CASE
WHEN extract(DAY   FROM now()) >= 25 THEN date_trunc('month', now())
WHEN extract(MONTH FROM now()) <=  2 THEN now()
ELSE                                      now() - interval '1 month'
END
, date_trunc('year' , now())
, interval '- 1 month'  -- negative interval
) t(request_detail)
ORDER  BY request_detail DESC;  -- redundant

负区间自动生成降序。您可以添加ORDER BY request_detail DESC以使其清晰和正确标准 SQL。

如果需要，可以将查询包装到函数中。

最重要的是，不要将generate_series()等集合返回函数包装在CASE语句中。这从来都不是一个好主意，但是从Postgres 10开始，它会引发一个错误：

ERROR:  set-returning functions are not allowed in CASE

演示：

在这里小提琴