我如何创建一个显示每月价值的视图

I have a view which is defined by the following code

我有一个由以下代码定义的视图

CREATE VIEW [dbo].V_SOME_VIEW AS
    WITH all_dates AS (SELECT DISTINCT(read_dtime) AS date FROM t_periodic_value),
            theObjects AS (SELECT * FROM t_object)    
    SELECT
        ad.date,
        objs.id, 
        pv1.value as theValue

    FROM all_dates ad
        LEFT JOIN theObjects objs ON
            objs.start_date <= ad.date AND (objs.end_date IS NULL OR (objs.end_date IS NOT NULL AND objs.end_date >= ad.date))
        LEFT JOIN t_periodic_value pv1 ON pv1.data_point_id = (SELECT id FROM t_data_point WHERE object_id = objs.id AND measurement_id = 'MonthlyValue')
            AND pv1.read_dtime = ad.date AND pv1.latest_ind = 1

GO

Which if I run a select for any given month gives me output along the lines of :

如果我在任何给定的月份运行select，就会得到如下输出:

Date       |     ID        |    theValue
01/01/1990 |  someFacility |      1000
02/01/1990 |  someFacility |      NULL
03/01/1990 |  someFacility |      NULL
...

and so on for the rest of the month. Nulls are returned for every date except the first as the value is calculated on a monthly basis. Is there a way I can define the view so that for every other day in the month, the value from the 1st is used?

在这个月剩下的时间里。除了第一个值是按月计算的以外，每个日期都返回null。是否有一种方法可以定义视图，以便每个月的每隔一天都使用1的值?

1 个解决方案

#1

Use a window function:

使用窗口函数:

CREATE VIEW [dbo].V_SOME_VIEW AS
    WITH all_dates AS (
          SELECT DISTINCT(read_dtime) AS date
          FROM t_periodic_value
        ),
        theObjects AS (  -- no idea why you are doing this
         SELECT *
         FROM t_object
        )    
    SELECT ad.date, objs.id, 
           SUM(pv1.value) OVER (PARTITION BY YEAR(ad.date), MONTH(ad.date)) as theValue
    FROM all_dates ad LEFT JOIN
         theObjects objs ON
         ON objs.start_date <= ad.date AND (objs.end_date IS NULL OR (objs.end_date IS NOT NULL AND objs.end_date >= ad.date)) LEFT JOIN
         t_periodic_value pv1
         ON pv1.data_point_id = (SELECT id FROM t_data_point WHERE object_id = objs.id AND measurement_id = 'MonthlyValue')
            AND pv1.read_dtime = ad.date AND pv1.latest_ind = 1

GO