Total count before each month

Question

I would like to calculate the total count before each individual month. Imagine that i have a table like this:

table "order": order_id(int), order_state(varchar2), changed_on(date)

order_state column has only 3 states - open, on_going and closed.

I would like to know before and for each month of the current fiscal year, how many where in on_going until each month.

example:

order_id	order_state	changed_on
3	OPEN	1-12-21
2	OPEN	1-12-21
1	ON_GOING	2-12-21
3	ON_GOING	4-12-21
23	ON_GOING	1-1-22
1	CLOSED	2-1-22

The results should be

count(*)	date
0	OCT-21
0	NOV-21
2	DEC-21
2	JAN-22
2	FEB-22
2	MAR-22
2	APR-22
2	MAI-22
0	JUN-22
0	JUL-22
0	AUG-22
0	SET-22

For 12-21 it should include order 1 and 3

For 1-22, order id 1 has been closed, include order 3 and 23

Assuming that current sysdate is May 22, until the end of the fiscal year it should return zero for future months.

This is where i'm at with the query

with fiscal_year_months as (

select to_char(add_months(to_date('01-10' || (to_number(to_char(sysdate, 'RRRR'))-1), 'DD-MM-RRRR', rownum -1), 'Month RRRR') mth from dual connect by rownum <=12 

) 

select count(*), changed_on
from orders
where order_state = 'ON_GOING'

right outer join fisca_year_months fym on fym.mth = orders.changed_on
order_by to_date(orders.changed_on, 'MM-RRRR')

but this query gives me the count for each individual month. And i want the count until the calculated month.

Answer 1

You can use a PARTITIONed OUTER JOIN and the LAST_VALUE analytic function to find the most recent statuses for each order and then can aggregate:

WITH calendar(month) AS (
  SELECT ADD_MONTHS(DATE '2021-10-01', LEVEL -1)
  FROM   DUAL
  CONNECT BY LEVEL <= 12
)
SELECT month,
       SUM(status) AS cnt
FROM   (
  SELECT month,
         order_id,
         MAX(
           CASE
           WHEN month <= SYSDATE AND status IN ('ON_GOING') THEN 1
           ELSE 0
           END
         ) AS status
  FROM   (
    SELECT c.month,
           t.order_id,
           LAST_VALUE(t.order_state) IGNORE NULLS
             OVER (PARTITION BY t.order_id ORDER BY c.month, t.changed_on)
             AS status
    FROM   calendar c
           LEFT OUTER JOIN table_name t
           PARTITION BY (t.order_id)
           ON (c.month = TRUNC(t.changed_on, 'MM'))
  )
  GROUP BY
         month,
         order_id
)
GROUP BY month;

Which, for your sample data:

CREATE TABLE table_name (order_id, order_state, changed_on) AS
SELECT  3, 'OPEN',     DATE '2021-12-01' FROM DUAL UNION ALL
SELECT  2, 'OPEN',     DATE '2021-12-01' FROM DUAL UNION ALL
SELECT  1, 'ON_GOING', DATE '2021-12-02' FROM DUAL UNION ALL
SELECT  3, 'ON_GOING', DATE '2021-12-04' FROM DUAL UNION ALL
SELECT 23, 'ON_GOING', DATE '2022-01-01' FROM DUAL UNION ALL
SELECT  1, 'CLOSED',   DATE '2022-01-02' FROM DUAL;

Outputs:

MONTH	CNT
2021-10-01 00:00:00	0
2021-11-01 00:00:00	0
2021-12-01 00:00:00	2
2022-01-01 00:00:00	2
2022-02-01 00:00:00	2
2022-03-01 00:00:00	2
2022-04-01 00:00:00	2
2022-05-01 00:00:00	2
2022-06-01 00:00:00	0
2022-07-01 00:00:00	0
2022-08-01 00:00:00	0
2022-09-01 00:00:00	0

db<>fiddle here

Answer 2

Here's revised version of your code:

magnet.go:

package main

import (
    "bufio"
    "bytes"
    "fmt"
    "io"
    "os"
    "strconv"
)

func nGroups(r io.Reader, w io.Writer) (int, error) {
    if _, ok := r.(*bufio.Reader); !ok {
        r = bufio.NewReader(r)
    }
    br := r.(*bufio.Reader)

    buf, err := br.ReadBytes('\n')
    if err != nil {
        return 0, err
    }
    n, err := strconv.Atoi(string(bytes.TrimSpace(buf)))
    if err != nil {
        return 0, err
    }

    ng := 0
    var prev byte
    for i := 0; i < n; i++ {
        m, err := br.ReadSlice('\n')
        if err != nil {
            if err != io.EOF || i < n-1 {
                return 0, err
            }
        }
        if m[0] != prev {
            ng++
        }
        prev = m[0]
    }
    return ng, nil
}

func main() {
    ng, err := nGroups(os.Stdin, os.Stdout)
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
    fmt.Println(ng)
}

---

A Go benchmark of 100,000 magnets seems to indicate that it's faster than your version.

rocka2q:
Benchmark100KR-8  1012    1177446 ns/op     4336 B/op       7 allocs/op
moogod:
Benchmark100KM-8     5  245167574 ns/op  5001715 B/op  200011 allocs/op