这个月的最后一天，在SQLPLUS中扭曲了

I would appreciate a little expert help please.

我很感激请一位专家帮忙。

in an SQL SELECT statement I am trying to get the last day with data per month for the last year. Example, I am easily able to get the last day of each month and join that to my data table, but the problem is, if the last day of the month does not have data, then there is no returned data. What I need is for the SELECT to return the last day with data for the month.

在SQL SELECT语句中,我试图获得去年每月数据的最后一天。例如,我很容易得到每个月的最后一天并将其加入我的数据表,但问题是,如果该月的最后一天没有数据,则没有返回的数据。我需要的是SELECT返回最后一天的月份数据。

This is probably easy to do, but to be honest, my brain fart is starting to hurt.

这可能很容易,但说实话,我的大脑屁开始受伤。

I've attached the select below that works for returning the data for only the last day of the month for the last 12 months.

我附上了下面的选项,用于返回过去12个月中该月的最后一天的数据。

Thanks in advance for your help!

在此先感谢您的帮助!

SELECT fd.cust_id,fd.server_name,fd.instance_name,
    TRUNC(fd.coll_date) AS coll_date,fd.column_name
FROM super_table fd,
    (SELECT TRUNC(daterange,'MM')-1 first_of_month
    FROM (
    select TRUNC(sysdate-365,'MM') + level as DateRange
    from    dual
    connect by level<=365)
    GROUP BY TRUNC(daterange,'MM')) fom
WHERE fd.cust_id = :CUST_ID
AND fd.coll_date > SYSDATE-400
AND TRUNC(fd.coll_date) = fom.first_of_month
GROUP BY fd.cust_id,fd.server_name,fd.instance_name,
    TRUNC(fd.coll_date),fd.column_name
ORDER BY fd.server_name,fd.instance_name,TRUNC(fd.coll_date)

3 个解决方案

#1

Here's another approach, if ANSI row_number() is supported:

如果支持ANSI row_number(),这是另一种方法:

with RevDayRanked(itemDate,rn) as (
  select
    cast(coll_date as date),
    row_number() over (
      partition by datediff(month,coll_date,'2000-01-01') -- rewrite datediff as needed for your platform
      order by coll_date desc
    )
  from super_table
)
  select itemDate
  from RevDayRanked
  where rn = 1;

Rows numbered 1 will be nondeterministically chosen among rows on the last active date of the month, so you don't need distinct. If you want information out of the table for all rows on these dates, use rank() over days instead of row_number() over coll_date values, so a value of 1 appears for any row on the last active date of the month, and select the additional columns you need:

编号为1的行将在该月的最后一个活动日期的行中进行非确定性选择,因此您不需要区分。如果您希望这些日期中所有行的信息不在表中,请使用rank()而不是row_umber()而不是row_date值,因此对于该月的最后一个活动日期的任何行,都会显示值1,并选择您需要的其他列:

with RevDayRanked(cust_id, server_name, coll_date, rk) as (
  select
    cust_id, server_name, coll_date,
    rank() over (
      partition by datediff(month,coll_date,'2000-01-01')
      order by cast(coll_date as date) desc
    )
  from super_table
)
  select cust_id, server_name, coll_date
  from RevDayRanked
  where rk = 1;

If row_number() and rank() aren't supported, another approach is this (for the second query above). Select all rows from your table for which there's no row in the table from a later day in the same month.

如果不支持row_number()和rank(),则另一种方法(对于上面的第二个查询)。从表中选择表中的所有行,从同一个月的某个月开始,表中没有行。

select
  cust_id, server_name, coll_date
from super_table as ST1
where not exists (
  select *
  from super_table as ST2
  where datediff(month,ST1.coll_date,ST2.coll_date) = 0
  and cast(ST2.coll_date as date) > cast(ST1.coll_date as date)
)

If you have to do this kind of thing a lot, see if you can create an index over computed columns that hold cast(coll_date as date) and a month indicator like datediff(month,'2001-01-01',coll_date). That'll make more of the predicates SARGs.

如果你必须做很多这样的事情,看看你是否可以在计算列上创建一个索引,该列包含cast(coll_date作为日期)和月份指标,如datediff(月,'2001-01-01',coll_date)。这将成为更多谓词SARG。

#2

You probably need to group your data so that each month's data is in the group, and then within the group select the maximum date present. The sub-query might be:

您可能需要对数据进行分组,以便每个月的数据都在组中,然后在组内选择最大日期。子查询可能是:

SELECT MAX(coll_date) AS last_day_of_month
    FROM Super_Table AS fd
    GROUP BY YEAR(coll_date) * 100 + MONTH(coll_date);

This presumes that the functions YEAR() and MONTH() exist to extract the year and month from a date as an integer value. Clearly, this doesn't constrain the range of dates - you can do that, too. If you don't have the functions in Oracle, then you do some sort of manipulation to get the equivalent result.

这假定存在YEAR()和MONTH()函数以从日期中提取年和月作为整数值。显然,这并不限制日期范围 - 你也可以这样做。如果您没有Oracle中的函数,那么您可以进行某种操作以获得相同的结果。

Using information from Rhose (thanks):

使用Rhose的信息(谢谢):

SELECT MAX(coll_date) AS last_day_of_month
    FROM Super_Table AS fd
    GROUP BY TO_CHAR(coll_date, 'YYYYMM');

This achieves the same net result, putting all dates from the same calendar month into a group and then determining the maximum value present within that group.

这实现了相同的净结果,将来自同一日历月的所有日期放入一个组中,然后确定该组中存在的最大值。

#3

Putting the above pieces together, would something like this work for you?

把上面的部分放在一起,这样的东西对你有用吗?

 SELECT fd.cust_id,
       fd.server_name,
       fd.instance_name,
       TRUNC(fd.coll_date) AS coll_date,
       fd.column_name
  FROM super_table fd,
 WHERE fd.cust_id = :CUST_ID
   AND TRUNC(fd.coll_date) IN (
                                SELECT MAX(TRUNC(coll_date))
                                  FROM super_table
                                 WHERE coll_date > SYSDATE - 400
                                   AND cust_id = :CUST_ID
                                 GROUP BY TO_CHAR(coll_date,'YYYYMM')
                              )
 GROUP BY fd.cust_id,fd.server_name,fd.instance_name,TRUNC(fd.coll_date),fd.column_name
 ORDER BY fd.server_name,fd.instance_name,TRUNC(fd.coll_date)

#1