不同日期的差异最大值和最小值

I'm going to try to explain this the best I can.

我将尽力解释这个问题。

The code below does the following:

以下代码执行以下操作:

• Finds a service address from the ServiceLocation table.

从ServiceLocation表中查找服务地址。

• Finds a service type (electric or water).

寻找服务类型(电力或水)。

• Finds how many days in the past to pull data.

查找过去提取数据的天数。

Once it has this, it calculates the "daily usage" by subtracting the max meter read for a day from the minimum meter read for a day.

一旦具有此功能,它将通过从一天中读取的最小仪表中减去一天的最大读数来计算“每日使用量”。

        (MAX(mr.Reading) - MIN(mr.Reading)) AS 'DaytimeUsage'

However, what I'm missing is the max reading from the day prior and the minimum reading from the current day. Mathematically, this should look something like this:

但是,我缺少的是从前一天的最大读数和当天的最小读数。在数学上,这应该看起来像这样:

• MAX(PriorDayReading) - MIN(ReadDateReading)

MAX(PriorDayReading) - MIN(ReadDateReading)

Essentially, if it goes back 5 days it should kick out a table that reads as follows:

基本上,如果它返回5天,它应该踢出一个如下所示的表:

Service Location | Read Date | Usage |

123 Main St | 4/20/15 | 12 |
123 Main St | 4/19/15 | 8 |
123 Main St | 4/18/15 | 6 |
123 Main St | 4/17/15 | 10 |
123 Main St | 4/16/15 | 11 |

123 Main St | 2015年4月20日| 12 | 123 Main St | 4/19/15 | 8 | 123 Main St | 4/18/15 | 6 | 123 Main St | 4/17/15 | 10 | 123 Main St | 4/16/15 | 11 |

Where "Usage" is the 'DaytimeUsage' + usage that I'm missing (and the question above). For example, 4/18/15 would be the 'DaytimeUsage' in the query below PLUS the the difference between the MAX read from 4/17/15 and the MIN read from 4/18/15.

“使用”是我缺少的'DaytimeUsage'+用法(以及上面的问题)。例如,4/18/15将是下面查询中的'DaytimeUsage',即从4/17/15读取的MAX与从4/18/15读取的MIN之间的差异。

I'm not sure how to accomplish this or if it is possible.

我不确定如何实现这个目标或者是否有可能。

SELECT 
    A.ServiceAddress AS 'Service Address', 
    convert(VARCHAR(10),A.ReadDate,101) AS 'Date', 
    SUM(A.[DaytimeUsage]) AS 'Usage' 

FROM 
(
    SELECT
        sl.location_addr AS 'ServiceAddress', 
        convert(VARCHAR(10),mr.read_date,101) AS 'ReadDate', 
        (MAX(mr.Reading) - MIN(mr.Reading)) AS 'DaytimeUsage'
    FROM
        DimServiceLocation AS sl
        INNER JOIN FactBill AS fb ON fb.ServiceLocationKey = sl.ServiceLocationKey
        INNER JOIN FactMeterRead as mr ON mr.ServiceLocationKey = sl.ServiceLocationKey
        INNER JOIN DimCustomer AS c ON c.CustomerKey = fb.CustomerKey
    WHERE 
        c.class_name = 'Tenant'
        AND sl.ServiceLocationKey = @ServiceLocation
        AND mr.meter_type = @ServiceType
    GROUP BY 
        sl.location_addr, 
        convert(VARCHAR(10),
        mr.read_date,101)
) A

WHERE A.ReadDate >= GETDATE()-@Days 
GROUP BY A.ServiceAddress, convert(VARCHAR(10),A.ReadDate,101)
ORDER BY convert(VARCHAR(10),A.ReadDate,101) DESC

3 个解决方案

#1

You can use the APPLY operator if you are above sql server 2005. Here is a link to the documentation. https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx The APPLY operation comes in two forms OUTER APPLY AND CROSS APPLY - OUTER works like a left join and CROSS works like an inner join. They let you run a query once for each row returned. I setup my own sample of what you were trying to do, here it is and I hope it helps.

如果您位于sql server 2005之上,则可以使用APPLY运算符。以下是文档的链接。 https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx APPLY操作有两种形式OUTER APPLY和CROSS APPLY - OUTER的工作方式类似于左连接,CROSS的工作方式类似于内部联接。它们允许您为返回的每一行运行一次查询。我设置了你自己想要做的样本,这是它,我希望它有所帮助。

http://sqlfiddle.com/#!6/fdb3f/1

CREATE TABLE SequencedValues (
  Location varchar(50) NOT NULL,
  CalendarDate datetime NOT NULL,
  Reading int
  )

INSERT INTO SequencedValues (
  Location,
  CalendarDate,
  Reading
  )
 SELECT
   'Address1',
   '4/20/2015',
   10
UNION SELECT
   'Address1',
   '4/19/2015',
   9
UNION SELECT
   'Address1',
   '4/19/2015',
   20
UNION SELECT
   'Address1',
   '4/19/2015',
   25
UNION SELECT
   'Address1',
   '4/18/2015',
   8
UNION SELECT
   'Address1',
   '4/17/2015',
   7
UNION SELECT
   'Address2',
   '4/20/2015',
   100
UNION SELECT
   'Address2',
   '4/20/2015',
   111
UNION SELECT
   'Address2',
   '4/19/2015',
   50
UNION SELECT
   'Address2',
   '4/19/2015',
   65


SELECT DISTINCT 
    sv.Location,
    sv.CalendarDate,
    sv_dayof.MINDayOfReading,
    sv_daybefore.MAXDayBeforeReading
FROM SequencedValues sv
OUTER APPLY (
    SELECT MIN(sv_dayof_inside.Reading) AS MINDayOfReading
    FROM SequencedValues sv_dayof_inside
    WHERE sv.Location = sv_dayof_inside.Location
    AND sv.CalendarDate = sv_dayof_inside.CalendarDate
    ) sv_dayof
OUTER APPLY (
    SELECT MAX(sv_daybefore_max.Reading) AS MAXDayBeforeReading
    FROM SequencedValues sv_daybefore_max
    WHERE sv.Location = sv_daybefore_max.Location
    AND sv_daybefore_max.CalendarDate IN (
        SELECT TOP 1 sv_daybefore_inside.CalendarDate
        FROM SequencedValues sv_daybefore_inside
        WHERE sv.Location = sv_daybefore_inside.Location
        AND sv.CalendarDate > sv_daybefore_inside.CalendarDate
        ORDER BY sv_daybefore_inside.CalendarDate DESC
        )
    ) sv_daybefore
ORDER BY
    sv.Location,
    sv.CalendarDate DESC

#2

It seems like you could solve this by just calculating the difference between the MAX of yesterday & today, however this is how I would approach it. Join to the same table again for the previous day relative to any given day, and select the Max/Min for that too within your inner query. Also if you place the date in the inner query where clause the data set you return will be quicker & smaller.

看起来你可以通过计算昨天和今天的MAX之间的差异来解决这个问题,但这就是我接近它的方法。相对于任何给定日期,前一天再次加入同一个表格,并在内部查询中选择最大/最小值。此外,如果将日期放在内部查询where子句中,则返回的数据集将更快更小。

SELECT 
A.ServiceAddress AS 'Service Address', 
  convert(VARCHAR(10),A.ReadDate,101) AS 'Date', 
  SUM(A.[TodayMax]) - SUM(A.[TodayMin]) AS 'Usage',
  SUM(A.[TodayMax]) - SUM(A.[YesterdayMax]) AS 'Usage with extra bit you want' 
FROM 
(
SELECT
    sl.location_addr AS 'ServiceAddress', 
    convert(VARCHAR(10),mr.read_date,101) AS 'ReadDate', 
    MAX(mrT.Reading) AS 'TodayMax',
    MIN(mrT.Reading) AS 'TodayMin',
    MAX(mrY.Reading) AS 'YesterdayMax',
    MIN(mrY.Reading) AS 'YesterdayMin',        
FROM
    DimServiceLocation AS sl
    INNER JOIN FactBill AS fb ON fb.ServiceLocationKey = sl.ServiceLocationKey
    INNER JOIN FactMeterRead as mrT ON mrT.ServiceLocationKey = sl.ServiceLocationKey
    INNER JOIN FactMeterRead as mrY ON mrY.ServiceLocationKey = s1.ServiceLocationKey
                                    AND mrY.read_date = mrT.read_date -1)
    INNER JOIN DimCustomer AS c ON c.CustomerKey = fb.CustomerKey
WHERE 
    c.class_name = 'Tenant'
    AND sl.ServiceLocationKey = @ServiceLocation
    AND mr.meter_type = @ServiceType
    AND convert(VARCHAR(10), mrT.read_date,101) >= GETDATE()-@Days 
GROUP BY 
    sl.location_addr, 
    convert(VARCHAR(10),
    mr.read_date,101)

) A
GROUP BY A.ServiceAddress, convert(VARCHAR(10),A.ReadDate,101)
ORDER BY convert(VARCHAR(10),A.ReadDate,101) DESC

#3

I'm not sure I full understood your db structure but I may have a solution so feel free to edit my answer to adapt or correct any mistake.

我不确定我是否完全理解你的数据库结构,但我可能有一个解决方案,所以随时编辑我的答案以适应或纠正任何错误。

The idea is to use two aliases for the table FactMeterRead. mrY (Y as yesterday) and mrT (T as Today). And differentiate them with a read_date restriction. However I didn't understand enough your tables to write a fully functional query. I hope you will get the idea anyway with this example.

我们的想法是为表FactMeterRead使用两个别名。 mrY(Y as as yesterday)和mrT(T as Today)。并使用read_date限制区分它们。但是我对你的表格不够了解,无法编写完整功能的查询。我希望你能用这个例子得到这个想法。

SELECT
        sl.location_addr AS 'ServiceAddress', 
        convert(VARCHAR(10),mrT.read_date,101) AS 'ReadDate', 
        (MAX(mrY.Reading) - MIN(mrT.Reading)) AS 'DaytimeUsage'
    FROM
        DimServiceLocation AS sl
          INNER JOIN FactMeterRead as mrY ON mrY.ServiceLocationKey = sl.ServiceLocationKey
          INNER JOIN FactMeterRead as mrT ON mrT.ServiceLocationKey = sl.ServiceLocationKey
    WHERE mrY.read_date=DATE_SUB(mrT.read_date,1 DAY)