QQ：按日期识别第一条记录 [英] QQ: identify the first record by date

问题描述

我希望能够在日期范围内按日期查找第一张和最后一张记录。我想在View而不是存储过程中执行此操作。



任何想法？



伪代码：

 创建 查看 VW_TEST 
  AS  
 
   TESTDATA  AS （
 选择 
 cast（getdate（）为  date ） as   date  
  union  全部 
 选择 dateadd（day，-1， datetime ）
 来自日期
 其中 datetime>（dateadd（day，-120，cast（cast（getdate（） as   date ） as   datetime ）） ）
）
 选择 
 t0。 date   as  firstInRange，
 t1。 date   as  lastInRange 
 来自 TESTDATA t0 
  left  外  join  TESTDATA t1   t1。 date  = cast（getdate（） as   date ）  -   今天 
 其中 t0。日期 = [first  date ] 
 
  GO  
 
 选择 firstInRange，lastInRange 
 来自 VW_TEST 
 其中 firstInRange>  @ RangeStart  
 和 lastInRange<  @ RangeEnd  

我甚至不确定这在我的意义上是否合理头。我会同时为它写一个SP，但最好有一个视图，因为它可以很好地插入我当前的框架。



让我知道你在想什么



谢谢^ _ ^

Andy



为了澄清，示例中的数据是假的。实际数据更加无序。每个日期都可能有一个记录，但在可能的情况下我们可能只有最后一周，或者数据可能不完整。



这是我写的SP在它的位置：

  ALTER   PROCEDURE  [dbo]。[SP_dashboard_list] 
（
  @ startdate   as   date ，
  @ enddate   as   date  
）
  AS  
 
  记录 as （
  select  r。*，ROW_NUMBER（） over （ partition   by  r.id  order   by   datetime ） as  row 
 来自 VW_results r 
 其中​​ r。 datetime > = < span class =code-sdkkeyword> @ startdate  
 和 r。 datetime < =  @ enddate  
），
首先 as （
 选择 * 来自记录其中 row =  1  
），
 lastrecord  as （
 选择 max（行）作为行，id 来自记录< span class =code-keyword> group   by  id 
），
最后 as （
  select  r。* 来自记录r 
 内部  join  lastrecord lr  on  r.id = lr.id 和 r.row = lr.row 
）
 
  SELECT  
 k.id，
 k.page_id，
 m.title，
 k.phrase，
 
 f.avg_r  as  init，
 l.avg_r  as  curr 
 
  FROM  pages m 
  inner  加入 dbo.ks k  on  m.page_id = k.page_id 
  left  外部 加入首先f   k .id = f.id 
  left  外部  join  last l  on  k.id = l.id 
 其中​​ 
 k.removed_date    null  

我希望清除它：S

解决方案

你不能有参数化视图，但是表值函数可以工作。



尝试这样的事情：

 创建 功能 dbo.TVF_dashboard_list 
（
  @ StartDate   As  < span class =code-keyword> date ，
  @ EndDate   As   date  
）
 返回 表 
 作为 
 返回 
（
  WITH 条记录 As  
 （
  SELECT  
 ROW_NUMBER（） OVER （ PARTITION   BY  R.id  ORDER   BY  R。[ datetime ]） As  RowAsc，
 ROW_NUMBER（）  OVER （ PARTITION   BY  R.id < span class =code-keyword> ORDER   BY  R。[ datetime ]  DESC ）作为 RowDesc，
 R.id，
 R.avg_r 
  FROM  
 dbo.VW_results  As  R 
  WHERE  
 R。[ datetime ] 介于  @ StartDate  和  @EndDate  
）
  SELECT  
 K.id，
 K.page_id，
 M.title，
 K.phrase，
 F.avg_r 作为 init，
 L.avg_r  As  curr 
  FROM  
 dbo.pages  As  M 
  INNER   JOIN  dbo.ks  As  K  ON  K.page_id = M.page_id 
  LEFT   JOIN 条记录作为 F  ON  F.id = K.id 和 F.RowAsc =  1  
  LEFT   JOIN 记录作为 L  ON  L.id = K.id 和 L.RowDesc =  1  
  WHERE  
 K.removed_date    Null  
）; 

使用它非常简单：

  SELECT  
 id，
 page_id，
 title，
短语，
 init，
 curr 
  FROM  
 db o.TVF_dashboard_list（ @ StartDate ， @ EndDate ）
; 

Hi,

I want to be able to find the first and last record by date from a date range. I would like to do this in a View rather than a stored proc.

Any ideas?

Pseudo code:

CREATE VIEW VW_TEST
AS

with TESTDATA AS(
  select
      cast(getdate() as date) as date
  union all
      select dateadd(day, -1, datetime)
      from dates
      where datetime>(dateadd(day,-120,cast(cast(getdate() as date) as datetime)))
)
select 
   t0.date as firstInRange,
   t1.date as lastInRange
from TESTDATA t0 
left outer join TESTDATA t1 on t1.date = cast(getdate() as date) --today
 where t0.date = [first date]

GO

select firstInRange,lastInRange
from VW_TEST
where firstInRange > @RangeStart 
and   lastInRange < @RangeEnd

I'm not even sure this makes sense in my head. I'll write up an SP for it in the mean time, but it would be good to have a view as it would slot nicely into my current framework.

Let me know what you think

Thanks ^_^
Andy

To clarify, the data in the example is fake. The actual data is much more disordered. There may well be a record for every date but in may cases we may only have the last week, or the data may be patchy.

Here is the SP I wrote in it's place:

ALTER PROCEDURE [dbo].[SP_dashboard_list]
(
@startdate as date,
@enddate as date
)
AS

with records as (
     select r.* ,ROW_NUMBER() over (partition by r.id order by datetime) as row
       from VW_results r
      where r.datetime >= @startdate
        and r.datetime <= @enddate
   ),
first as(
   select * from records where row = 1
) ,
lastrecord as (
	select max(row) as row, id from records group by id
),
last as (
   select r.* from records r
	inner join lastrecord lr on r.id = lr.id and r.row = lr.row
)

SELECT
	k.id,
	k.page_id,
	m.title,
	k.phrase,

	f.avg_r as init,
	l.avg_r as curr
	
FROM  pages m
	  inner join dbo.ks k on m.page_id = k.page_id
	  left outer join first f on k.id = f.id
	  left outer join last l on k.id = l.id
where 
	k.removed_date is null

I hope that clears it up :S

解决方案

You can't have a parameterized view, but a table-valued function would work.

Try something like this:

CREATE FUNCTION dbo.TVF_dashboard_list
(
    @StartDate As date,
    @EndDate As date
)
Returns Table
As
Return
(
    WITH records As
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY R.id ORDER BY R.[datetime]) As RowAsc,
            ROW_NUMBER() OVER (PARTITION BY R.id ORDER BY R.[datetime] DESC) As RowDesc,
            R.id,
            R.avg_r
        FROM
            dbo.VW_results As R
        WHERE
            R.[datetime] Between @StartDate And @EndDate
    )
    SELECT
        K.id,
        K.page_id,
        M.title,
        K.phrase,
        F.avg_r As init,
        L.avg_r As curr
    FROM
        dbo.pages As M
        INNER JOIN dbo.ks As K ON K.page_id = M.page_id
        LEFT JOIN records As F ON F.id = K.id And F.RowAsc = 1
        LEFT JOIN records As L ON L.id = K.id And L.RowDesc = 1
    WHERE
        K.removed_date Is Null
);

Using it is fairly simple:

SELECT
    id,
    page_id,
    title,
    phrase,
    init,
    curr
FROM
    dbo.TVF_dashboard_list(@StartDate, @EndDate)
;

这篇关于QQ：按日期识别第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！