Does anyone here know SQL well? Help needed.  (Read 206 times)

Piglet

  • 1337
  • *
  • Posts: 2851
  • Country: gb
Does anyone here know SQL well? Help needed.
« on: November 20, 2021, 12:19 »
I can write a program to do this, but was wondering whether there is a way to do it in a single SQL statement.

A table has fields including: timestamp, p, s, and a.

I'm looking for a command to select (and when I'm happy it working, delete) all but the latest 30 timestamp'd entries for each (p, s, and a) combination.

I could write a program to to do it but I wonder if a single complex sql statement could do it...

This is the pseudo-code for what the program woud look like:

Code: [Select]

select distinct s,p,a from tablename;

for each ($s,$p,$a) row returned{

select min(timestamp) as $mt from (select timestamp from tablename where p = $p and s=$s and a = $a order by timestamp desc limit 30 ) as s1;

select/delete from tablename where timestamp <  $mt and p = $p and s=$s and a = $a;
}


Caracal

  • Sr. Member
  • *
  • Posts: 132
  • Country: ru
    • caracalRus
Re: Does anyone here know SQL well? Help needed.
« Reply #1 on: November 20, 2021, 13:38 »
select * from
(
select id, s, p, a, timestamp, row_number() over (partition by s,p,a order by timestamp desc) as numeration
from table
) where numeration > 30

Caracal

  • Sr. Member
  • *
  • Posts: 132
  • Country: ru
    • caracalRus
Re: Does anyone here know SQL well? Help needed.
« Reply #2 on: November 21, 2021, 12:57 »
Piglet, did it help?

Piglet

  • 1337
  • *
  • Posts: 2851
  • Country: gb
Re: Does anyone here know SQL well? Help needed.
« Reply #3 on: November 21, 2021, 13:16 »
Hmmm. No joy.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(partition by....

I'm using "mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1"

Maybe "partition by" is not available...

Caracal

  • Sr. Member
  • *
  • Posts: 132
  • Country: ru
    • caracalRus
Re: Does anyone here know SQL well? Help needed.
« Reply #4 on: November 21, 2021, 16:09 »
I dont have an experience in Mysql, but this construction should work in Mysql. Maybe there are some other problems with namings.

Tested in Oracle, works perfectly:
Code: [Select]
select * from (
select EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, JOB_ID, HIRE_DATE,
row_number() over (partition by DEPARTMENT_ID, JOB_ID order by HIRE_DATE desc) as numeration
from EMPLOYEES
order by DEPARTMENT_ID, JOB_ID
) where numeration > 1

Pyc

  • Newbie
  • *
  • Posts: 1
  • Country: ru
Re: Does anyone here know SQL well? Help needed.
« Reply #5 on: November 21, 2021, 18:08 »
Hey guys, registered specially to answer this.

MySQL does not have row_number() function, but we can emulate it.

This SQL we can use to select (tested with MySQL 5.7):

Code: [Select]
SELECT
    s, p, a, ts, rown
FROM
    (SELECT
        s, p, a, ts,
        @rown := IF(@prevA = a AND @prevP = p AND @prevS = s, @rown + 1, 1) AS rown,
        @prevA := a, @prevP := p, @prevS := s
    FROM tablename
    JOIN (SELECT @prevA := NULL, @prevP := NULL, @prevS := NULL, @rown := 0) AS vars
    ORDER BY s, p, a, ts DESC) temp
WHERE rown <= 30;

... and this one to delete:

Code: [Select]
DELETE t FROM
    tablename t,
    (SELECT
        s, p, a, MIN(ts) AS mints
    FROM
        (SELECT
            s, p, a, ts,
            @rown := IF(@prevA = a AND @prevP = p AND @prevS = s, @rown + 1, 1) AS rown,
            @prevA := a, @prevP := p, @prevS := s
        FROM tablename
        JOIN (SELECT @prevA := NULL, @prevP := NULL, @prevS := NULL, @rown := 0) AS vars
        ORDER BY s, p, a, ts DESC) AS temp
    WHERE rown <= 30
    GROUP BY s, p, a) lim
WHERE lim.s = t.s
    AND lim.p = t.p
    AND lim.a = t.a
    AND lim.mints > t.ts;