Results 1 to 12 of 12

Thread: Moving Average

  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Unanswered: Moving Average

    Hola everyone,

    I am attempting to find a moving average for gas production per gas well over a 3 month period. I also would like to only keep the records in which the average of the previous 3 months is less than or equal to 90. My table roughly looks as such:

    Code:
    OperatorID RRCID Year Month Gas Production
    886261	13	2008	1	3
    886261	13	2008	2	9
    886261	13	2008	3	5
    886261	13	2008	4	5
    886261	13	2008	5	5
    886261	13	2008	8	5
    886261	13	2008	9	5
    886261	13	2008	10	4
    886261	13	2008	11	6
    886261	13	2008	12	6
    886261	13	2009	1	6
    886261	14	2008	1	752
    886261	14	2008	2	697
    886261	14	2008	3	673
    886261	14	2008	4	706
    886261	14	2008	5	701
    886261	14	2008	8	730
    886261	14	2008	9	716
    886261	14	2008	10	717
    886261	14	2008	11	680
    I would like to find the average of the last 3 months, or last 3 records. Any assistance would be greatly appreciated.
    Last edited by Pat Phelan; 06-25-12 at 16:29. Reason: Added code tags to make results more readable

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    What would be the expected result for this sample data?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by tech_wiz2008 View Post
    I would like to find the average of the last 3 months, or last 3 records.
    Two very different criteria. Which do you want?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2012
    Posts
    16
    I would like the last 3 months. The expected result I would like to look as such:

    Original Data:
    OperatorID RRCID Year Month Gas Production
    886261 13 2008 1 3
    886261 13 2008 2 9
    886261 13 2008 3 5
    886261 13 2008 4 5
    886261 13 2008 5 5
    886261 13 2008 8 5
    886261 13 2008 9 5
    886261 13 2008 10 4
    886261 13 2008 11 6
    886261 13 2008 12 6
    886261 13 2009 1 6
    886261 14 2008 1 752
    886261 14 2008 2 697
    886261 14 2008 3 673
    886261 14 2008 4 706
    886261 14 2008 5 701
    886261 14 2008 8 730
    886261 14 2008 9 716
    886261 14 2008 10 717
    886261 14 2008 11 680

    Expected Results:

    886261 13 2008 3 5.6
    886261 13 2008 4 6.3
    886261 13 2008 5 5

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    Why the months 06/2008, 07/2008, 08/2008, 09/2008, 10/2008, 11/2008, 12/2008 and 01/2009 are not in the expected result?
    What should be the result for the month 08/2008?

  6. #6
    Join Date
    Jan 2012
    Posts
    16
    My fault, they would be the expected results just didn't want to list them out and I forgot to put etc.

    Expected Results:

    886261 13 2008 3 5.6
    886261 13 2008 4 6.3
    886261 13 2008 5 5
    886261 13 2008 10 4.6
    886261 13 2008 11 5
    886261 13 2008 12 5.3

    The months 6, 7 are not included cause there is no reported production for that month and year. The months 8 and 9 wouldn't be included because they don't have 3 months to sum with to make an average.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       OperatorID       INT
    ,  RRCID            INT
    ,  [Year]           INT
    ,  [Month]          INT
    ,  [Gas Production] INT
       )
    
    INSERT INTO @t
              SELECT 886261, 13, 2008,  1, 3
    UNION ALL SELECT 886261, 13, 2008,  2, 9
    UNION ALL SELECT 886261, 13, 2008,  3, 5
    UNION ALL SELECT 886261, 13, 2008,  4, 5
    UNION ALL SELECT 886261, 13, 2008,  5, 5
    UNION ALL SELECT 886261, 13, 2008,  8, 5
    UNION ALL SELECT 886261, 13, 2008,  9, 5
    UNION ALL SELECT 886261, 13, 2008, 10, 4
    UNION ALL SELECT 886261, 13, 2008, 11, 6
    UNION ALL SELECT 886261, 13, 2008, 12, 6
    UNION ALL SELECT 886261, 13, 2009,  1, 6
    UNION ALL SELECT 886261, 14, 2008,  1, 752
    UNION ALL SELECT 886261, 14, 2008,  2, 697
    UNION ALL SELECT 886261, 14, 2008,  3, 673
    UNION ALL SELECT 886261, 14, 2008,  4, 706
    UNION ALL SELECT 886261, 14, 2008,  5, 701
    UNION ALL SELECT 886261, 14, 2008,  8, 730
    UNION ALL SELECT 886261, 14, 2008,  9, 716
    UNION ALL SELECT 886261, 14, 2008, 10, 717
    UNION ALL SELECT 886261, 14, 2008, 11, 680
    
    SELECT z.OperatorID, z.RRCID, z.[Year], z.[Month], z.[Gas Production]
    ,  AVG(1e0 * d.[Gas Production]) AS 'Moving Ave'
       FROM @t AS z
       JOIN @t AS d
          ON (d.OperatorID = z.OperatorID
          AND d.RRCID = z.RRCID
          AND DATEADD(m, 12 * (d.[year] - 1950) + d.[month], '1949-12-01')
             BETWEEN DATEADD(m, 12 * (z.[year] - 1950) + z.[month], '1949-10-01')
                AND DATEADD(m, 12 * (z.[year] - 1950) + z.[month], '1949-12-01'))
       GROUP BY z.OperatorID, z.RRCID, z.[Year], z.[Month], z.[Gas Production]
       HAVING 3 = COUNT(*)
          AND Avg(d.[Gas Production]) <= 90
    There's a lot simpler solution if you are running SQL 2012, but this should work on Microsoft SQL 2005 and later and could be easily kludged to work on earlier versions too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2012
    Posts
    16
    Is there a way to run it with about 4 million records, and numerous operartor ids and lease numbers (RRCID)?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you considered using something such as exponential smoothing. granted its not exactly the same as a moving average, but the correct choice of smoothing factor gets pretty close to a moving average. and computationally its a heck of a lot less hungry to run.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2012
    Posts
    16
    I haven't considered it yet, but I'm willing to try anything. It just has to give similar results that a moving average would produce, because we're using this information to target certain wells.

    Would you happen to have any advice or sample code on how to use exponential smoothing?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tech_wiz2008 View Post
    Is there a way to run it with about 4 million records, and numerous operartor ids and lease numbers (RRCID)?
    Without knowing exactly what you require and what resources you are using, the only answer I can give is "Certainly". I routinely use similar code on 500+ million row tables and get answers in under ten minutes.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •