__vasilich__ Comments - Answer Overflow

__vasilich__

Posts Comments

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

second one

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

WITH RECURSIVE cte AS (
  SELECT
    e1.cid,
    e1.date AS start_date,
    e2.date AS end_date
  FROM events e1 
  LEFT JOIN events e2 ON e1.cid = e2.cid AND e2.type = "end"
  WHERE e1.type = "start" 
  ORDER BY e1.cid
),
minutes_cte AS (
  SELECT 
    cid,
    DATETIME(cte.start_date) AS minutes,
    end_date
  FROM cte
  
  UNION ALL
  
  SELECT 
    cid,
    DATETIME(minutes, '+1 minute'),
    end_date
  FROM minutes_cte
  WHERE DATETIME(minutes, '+1 minute') <= DATETIME(end_date)
),
conc AS (
  SELECT
    COUNT(*) OVER (ORDER BY minutes) AS id,
    minutes,
    COUNT(*) AS concurrent_events
  FROM minutes_cte
  GROUP BY minutes
  ORDER BY minutes ASC, concurrent_events DESC
),
MaxCev AS (
  SELECT MAX(concurrent_events) AS max_cev
  FROM conc
),
ResultTable as (SELECT 
  period_start, period_end, (SELECT max_cev FROM MaxCev) AS event_count 
  FROM (
    SELECT COUNT(*) over (ORDER BY c.minutes) AS id, c.minutes AS period_start 
      FROM conc c 
      LEFT JOIN conc p ON p.id = c.id - 1 
      JOIN MaxCev m ON c.concurrent_events = m.max_cev 
      WHERE c.concurrent_events > p.concurrent_events
  ) starts 
  LEFT JOIN 
    (
       SELECT COUNT(*) OVER (ORDER BY minutes) AS id, minutes AS period_end
      FROM conc 
      WHERE id IN 
      (
        SELECT p.id
        FROM conc c 
        LEFT JOIN conc p ON c.id = p.id + 1 
        JOIN MaxCev m ON p.concurrent_events = m.max_cev 
        WHERE c.concurrent_events < p.concurrent_events
      )
    ) ends USING(id))
SELECT * FROM (SELECT * FROM ResultTable LIMIT 1)
UNION ALL
SELECT * FROM (SELECT * FROM ResultTable
ORDER BY period_start DESC LIMIT 1)

WITH RECURSIVE cte AS (
  SELECT
    e1.cid,
    e1.date AS start_date,
    e2.date AS end_date
  FROM events e1 
  LEFT JOIN events e2 ON e1.cid = e2.cid AND e2.type = "end"
  WHERE e1.type = "start" 
  ORDER BY e1.cid
),
minutes_cte AS (
  SELECT 
    cid,
    DATETIME(cte.start_date) AS minutes,
    end_date
  FROM cte
  
  UNION ALL
  
  SELECT 
    cid,
    DATETIME(minutes, '+1 minute'),
    end_date
  FROM minutes_cte
  WHERE DATETIME(minutes, '+1 minute') <= DATETIME(end_date)
),
conc AS (
  SELECT
    COUNT(*) OVER (ORDER BY minutes) AS id,
    minutes,
    COUNT(*) AS concurrent_events
  FROM minutes_cte
  GROUP BY minutes
  ORDER BY minutes ASC, concurrent_events DESC
),
MaxCev AS (
  SELECT MAX(concurrent_events) AS max_cev
  FROM conc
),
ResultTable as (SELECT 
  period_start, period_end, (SELECT max_cev FROM MaxCev) AS event_count 
  FROM (
    SELECT COUNT(*) over (ORDER BY c.minutes) AS id, c.minutes AS period_start 
      FROM conc c 
      LEFT JOIN conc p ON p.id = c.id - 1 
      JOIN MaxCev m ON c.concurrent_events = m.max_cev 
      WHERE c.concurrent_events > p.concurrent_events
  ) starts 
  LEFT JOIN 
    (
       SELECT COUNT(*) OVER (ORDER BY minutes) AS id, minutes AS period_end
      FROM conc 
      WHERE id IN 
      (
        SELECT p.id
        FROM conc c 
        LEFT JOIN conc p ON c.id = p.id + 1 
        JOIN MaxCev m ON p.concurrent_events = m.max_cev 
        WHERE c.concurrent_events < p.concurrent_events
      )
    ) ends USING(id))
SELECT * FROM (SELECT * FROM ResultTable LIMIT 1)
UNION ALL
SELECT * FROM (SELECT * FROM ResultTable
ORDER BY period_start DESC LIMIT 1)

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

KISS not even close)

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

yeah, you right

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

I agree

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

so to get first row I can use LIMIT 1, and for the last one ORDER BY period_start DESC LIMIT 1, is there a simple way to get both?

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

THANKS!!! as far as I know sql was made for regular people to understand, but in my mind it hasn't clicked yet. Basic stuff are understandable though

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

at the moment I'm trying to figure out how to get last period, and both(first and last)

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

yeah, they're unique

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

I suspected it would require to do subqueries, but I hoped it would be simpler)

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

@Jochem ok, I finally got time to check you code(sorry about that), it does work, thanks!

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

WOW... I'll check it all later, bit busy at the moment, but on the first glance it's way beyond my knowledge)

50 replies

KPCKevin Powell - Community

•Created by __vasilich__ on 7/20/2023 in #back-end

Select ranges with max simultaneous processes (SQLite)

cid - process id, I think it's not needed, count should be calculated - the count of processes running in the range. so on 1 row we have 1 process running, on 2 - 2, on 3 - 3, on 4 - 2 because one is ended, so on 3 row was the start of the range and on 4 row is the end, and count is 3. that's how we get

{
  start: "2021-01-01 03:00:00",
  end: "2021-01-01 06:00:00",
  count: 3
}

{
  start: "2021-01-01 03:00:00",
  end: "2021-01-01 06:00:00",
  count: 3
}

50 replies

Gaming

Programming