Rolling Rows in SQL Table

Rolling count of rows withing time interval

Sounds like an application for window functions. But, sadly, that's not the case. Window frames can only be based on row counts, not on actual column values.

A simple query with LEFT JOIN can do the job:

SELECT t0.order_id
     , count(t1.time_created) AS count_within_3_sec
FROM   tbl      t0
LEFT   JOIN tbl t1 ON t1.time_created BETWEEN t0.time_created - interval '3 sec'
                                          AND t0.time_created
GROUP  BY 1
ORDER  BY 1;

db<>fiddle here

Does not work with time like in your minimal demo, as that does not wrap around. I suppose it's reasonable to assume timestamp or timestamptz.

Since you include each row itself in the count, an INNER JOIN would work, too. (LEFT JOIN is still more reliable in the face of possible NULL values.)

Or use a LATERAL subquery and you don't need to aggregate on the outer query level:

SELECT t0.order_id
     , t1.count_within_3_sec
FROM   tbl t0
LEFT   JOIN LATERAL (
   SELECT count(*) AS count_within_3_sec
   FROM   tbl t1
   WHERE  t1.time_created BETWEEN t0.time_created - interval '3 sec'
                              AND t0.time_created
   ) t1 ON true
ORDER  BY 1;

Related:

• Rolling sum / count / average over date interval

For big tables and many rows in the time frame, a procedural solution that walks through the table once will perform better. Like:

• Window Functions or Common Table Expressions: count previous rows within range
• Alternatives to broken PL/ruby: convert a warehouse journal table
• GROUP BY and aggregate sequential numeric values

Rolling Multiple Rows into a Single Row Based on Matching Data in SQL Server

Assuming this is for small (ish) amount of data, you can correlate the subquery.
It nests (so the query can be thought of as running once per row in the outer query.
The optimiser normally optimises better than that but depends a bit on volumes as to whether it works for you better than other approaches.

DROP TABLE #tags
GO

CREATE TABLE #tags (ReferenceNumber varchar(10), TagId varchar(20)) 
GO

INSERT INTO #tags (ReferenceNumber , TagId ) 
SELECT 114942, 1
UNION SELECT 114942, 2
UNION SELECT 114942, 3
UNION SELECT 114942, 4
UNION SELECT 123456, 1
GO

SELECT ReferenceNumber,
       stuff((select '; ' + TagID from #Tags AS CorrTagTable WHERE CorrTagTable.ReferenceNumber = Reftagtable.ReferenceNumber
for xml path('')), 1, 1,'')[Tags]
from #tags AS RefTagTable
order by ReferenceNumber

GO 

Produces:

ReferenceNumber Tags
114942          1; 2; 3; 4
114942          1; 2; 3; 4
114942          1; 2; 3; 4
114942          1; 2; 3; 4
123456          1

SQL Query for 7 Day Rolling Average in SQL Server

Try:

select x.*,
       avg(dailyusage) over(partition by productid order by productid, date rows between 6 preceding and current row) as rolling_avg
  from (select productid, date, sum(usagecount) as dailyusage
          from tbl
         group by productid, date) x

Fiddle:

http://sqlfiddle.com/#!6/f674a7/4/0

Replace "avg(dailusage) over...." with sum (rather than avg) if what you really want is the sum for the past week. In your title you say you want the average but later you say you want the sum. The query should be the same other than that, so use whichever you actually want.

As was pointed out by Gordon this is basically the average of the past 6 dates in which the product was used, which might be more than just the past 6 days if there are days without any rows for that product on the table because it wasn't used at all. To get around that you could use a date table and your products table.

Rolling up multiple rows into a single row and column for SQL Server data

Your statement begins with

SELECT a.CLAIMNO, STUFF((...inner select query that aliases table a...))

It is the inner select query that aliases table a, not the outer one. A.claimno is not a thing/doesn’t exist by the time processing the sql gets to that outer level

Here is a simpler example:

select a.thing, (select a.otherthing from table a)

a.thing does not exist outside the brackets. These are valid

It isn’t really clear what you’re trying to do so I can’t advise on what you should do to achieve the result you want, I can only answer why you’re getting this error

You’d be better off removing the outer query, and the stuff call and just posting up your inner query as a new question with “this produces (paste results) and I want it to produce (create desired results), please help”

Calculating a rolling 30 day average with multiple values per day

You want to use RANGE instead of ROWS in your window frame. But that requires ordering by a number. So you need to do:

over (... order by datediff(date,'1970-01-01') asc range between 31 preceding and 1 following)

(Though I think you want 0 following?)

Rolling sum previous 12 months per month (SQL- Snowflake)

Seems to work - but you need an additional nested query to build the running sum and a filter value to remove the first 12 months:

WITH
-- your input, don't use in final query ..
indata(dt,New_Customers) AS (
          SELECT DATE '2021-04-01',4 UNION ALL SELECT DATE '2021-05-01',1
UNION ALL SELECT DATE '2021-06-01',2 UNION ALL SELECT DATE '2021-07-01',6
UNION ALL SELECT DATE '2021-08-01',3 UNION ALL SELECT DATE '2021-09-01',2
UNION ALL SELECT DATE '2021-10-01',3 UNION ALL SELECT DATE '2021-11-01',8
UNION ALL SELECT DATE '2021-12-01',3 UNION ALL SELECT DATE '2022-01-01',4
UNION ALL SELECT DATE '2022-02-01',0 UNION ALL SELECT DATE '2022-03-01',3
UNION ALL SELECT DATE '2022-04-01',3 UNION ALL SELECT DATE '2022-05-01',2
UNION ALL SELECT DATE '2022-06-01',3 UNION ALL SELECT DATE '2022-07-01',1
UNION ALL SELECT DATE '2022-08-01',3 UNION ALL SELECT DATE '2022-09-01',2
UNION ALL SELECT DATE '2022-10-01',3 UNION ALL SELECT DATE '2022-11-01',1
UNION ALL SELECT DATE '2022-12-01',6 UNION ALL SELECT DATE '2023-01-01',8
UNION ALL SELECT DATE '2023-02-01',4
)
-- real query starts here, replace following comma with "WITH"
,
-- need to build the row number and the running sum first in a subselect ...
olap AS (
  SELECT
    dt
  , SUM(new_customers) OVER(ORDER BY dt ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS running_cust_count
  , ROW_NUMBER()       OVER(ORDER BY dt                                          ) AS rownum
  FROM indata
) 
SELECT
  dt AS rep_date
, running_cust_count
FROM olap
WHERE rownum >= 12
;
-- out   rep_date  | running_cust_count 
-- out ------------+--------------------
-- out  2022-03-01 |                 39
-- out  2022-04-01 |                 38
-- out  2022-05-01 |                 39
-- out  2022-06-01 |                 40
-- out  2022-07-01 |                 35
-- out  2022-08-01 |                 35
-- out  2022-09-01 |                 35
-- out  2022-10-01 |                 35
-- out  2022-11-01 |                 28
-- out  2022-12-01 |                 31
-- out  2023-01-01 |                 35
-- out  2023-02-01 |                 39

---

Related Topics