Find All Sequences with the Same Column Value

Finding all sequences in table

You can use the LEAD and LAG analytical functions as follows:

select distinct userid from
(select t.*, 
        lead(action) over (partition by userid order by timestamp) as lead_Action,
        lag(action) over (partition by userid order by timestamp) as lag_Action from t)
where lag_Action = 'X1' and action = 'X2' and lead_Action = 'X3';

Get all sequences with current values

In Postgres 12, you can use pg_sequences:

select schemaname as schema, 
       sequencename as sequence, 
       last_value
from pg_sequences

How can I count data groups sequential order in the same column in SQL Server 2014?

Below query finds duplicate patterns for 2, 3, 4 and 5 repeating rows.

It uses 'LEAD' and 'HASHBYTES' functions.

Query works by computing a hash sequence for values in current row + following rows and then grouping on these hash values to find "duplicate" patterns. This process is done for each row.

Note: an ever increasing sequence column (to denote row position) i.e. ID is assumed.

CREATE TABLE #Data( ID INT IDENTITY PRIMARY KEY, Val VARCHAR( 20 ))
INSERT INTO #Data
VALUES
( '3E010000' ), ( '2010000' ), ( '2010007' ), ( '2010008' ), ( '2010000' ),
( '2010003' ), ( '2010009' ), ( '0201000A' ), ( '0B01000C' ), ( '2010002' ),
( '3E010000' ), ( '2010000' ), ( '2010007' ), ( '0B010014' ), ( '2010009' ),
( '0201000A' ), ( '0B01000C' ), ( '2010002' )

SELECT Pat3Rows, COUNT(*) AS Cnt
FROM(
    SELECT *,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID )) AS Pat2Rows,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + LEAD( Val, 2, '' ) OVER( ORDER BY ID )) AS Pat3Rows,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + LEAD( Val, 3, '' ) OVER( ORDER BY ID )) AS Pat4Rows,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + LEAD( Val, 3, '' ) OVER( ORDER BY ID ) + LEAD( Val, 4, '' ) OVER( ORDER BY ID )) AS Pat5Rows
    FROM #Data AS D1
) AS HashedGroups
GROUP BY Pat3Rows
HAVING COUNT(*) > 1

Note: there is a possibility, albeit extremely remote, of encountering hash collisions, so the above logic is not guaranteed to handle all theoretically possible cases. In summary I would not recommend using it if someone's life depends on the procedure to always be 100% accurate.

You did not specify how the output should look like so I will leave this up to you.

I have also tested this on my laptop with 18,000 rows and it produces a result in less than 1 second.

Sample use case:

;WITH DataHashed AS(
SELECT *,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID )) AS Pat2Rows,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + ',' + LEAD( Val, 2, '' ) OVER( ORDER BY ID )) AS Pat3Rows,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + ',' + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + ',' +  LEAD( Val, 3, '' ) OVER( ORDER BY ID )) AS Pat4Rows,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + ',' + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + ',' +  LEAD( Val, 3, '' ) OVER( ORDER BY ID ) + ',' +  LEAD( Val, 4, '' ) OVER( ORDER BY ID )) AS Pat5Rows
FROM #Data ),
RepeatingPatterns AS(
    SELECT MIN( ID ) AS FirstRow, Pat2Rows AS PatternHash, 2 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat2Rows HAVING COUNT(*) > 1
    UNION ALL
    SELECT MIN( ID ) AS FirstRow, Pat3Rows, 3 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat3Rows HAVING COUNT(*) > 1
    UNION ALL
    SELECT MIN( ID ) AS FirstRow, Pat4Rows, 4 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat4Rows HAVING COUNT(*) > 1
    UNION ALL
    SELECT MIN( ID ) AS FirstRow, Pat5Rows, 5 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat5Rows HAVING COUNT(*) > 1
)
--SELECT * FROM RepeatingPatterns
SELECT 
     CONVERT( VARCHAR( 50 ), SUBSTRING(
        ( SELECT ',' + D.Val  AS [text()]
        FROM #Data AS D
        WHERE RP.FirstRow <= D.ID AND D.ID < ( RP.FirstRow + RP.PatternSize )
        ORDER BY D.ID
        FOR XML PATH ('')
        ), 2, 1000 )) AS Pattern, CONVERT( VARCHAR( 35 ), PatternHash, 1 ) AS PatternHash, RP.PatternSize, Cnt
FROM RepeatingPatterns AS RP

Sample output:

Pattern                                            PatternHash                         PatternSize Cnt
-------------------------------------------------- ----------------------------------- ----------- -----------
0201000A,0B01000C                                  0x499D8B1750A9BF57795B4D60D58DCF81  2           2
2010000,2010007                                    0x7EDE1E675D934F3035DACAC53F74DD14  2           2
3E010000,2010000                                   0x85FBFD817CFBB9BD08E983671EB594B7  2           2
2010009,0201000A                                   0x8E18E36B989BD859AF039238711A7F8C  2           2
0B01000C,2010002                                   0xF1EABB115FB3AEF2D162FB3EC7B6AFDA  2           2
0201000A,0B01000C,2010002                          0x6DE203B38A13501881610133C1EDBF85  3           2
2010009,0201000A,0B01000C                          0x9EB3ACFE8580A39FC530C7CA54830602  3           2
3E010000,2010000,2010007                           0xE414661F54C985B7ED9FA82FF05C1219  3           2
2010009,0201000A,0B01000C,2010002                  0x7FCDB748E37A6F6299AE8B269A4B0E49  4           2

Postgresql - SQL query to list all sequences in database

The following query should to work:

create table foo(id serial, v integer);
create table boo(id_boo serial, v integer);
create sequence omega;
create table bubu(id integer default nextval('omega'), v integer);

select sn.nspname as seq_schema,
       s.relname as seqname,
       st.nspname as tableschema,
       t.relname as tablename,
       at.attname as columname
  from pg_class s
  join pg_namespace sn on sn.oid = s.relnamespace
  join pg_depend d on d.refobjid = s.oid 
  join pg_attrdef a on d.objid = a.oid
  join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
  join pg_class t on t.oid = a.adrelid
  join pg_namespace st on st.oid = t.relnamespace
 where s.relkind = 'S'
   and d.classid = 'pg_attrdef'::regclass
   and d.refclassid = 'pg_class'::regclass;
┌────────────┬────────────────┬─────────────┬───────────┬───────────┐
│ seq_schema │    seqname     │ tableschema │ tablename │ columname │
╞════════════╪════════════════╪═════════════╪═══════════╪═══════════╡
│ public     │ foo_id_seq     │ public      │ foo       │ id        │
│ public     │ boo_id_boo_seq │ public      │ boo       │ id_boo    │
│ public     │ omega          │ public      │ bubu      │ id        │
└────────────┴────────────────┴─────────────┴───────────┴───────────┘
(3 rows)

For calling sequence related functions you can use s.oid column. For this case, it is sequence unique oid identifier. You need cast it to regclass.

A script for you request can looks like:

do $$
declare
  r record;
  max_val bigint;
begin
  for r in
    select s.oid as seqoid,
           at.attname as colname,
           a.adrelid as reloid
      from pg_class s
      join pg_namespace sn on sn.oid = s.relnamespace
      join pg_depend d on d.refobjid = s.oid 
      join pg_attrdef a on d.objid = a.oid
      join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
     where s.relkind = 'S'
       and d.classid = 'pg_attrdef'::regclass 
       and d.refclassid = 'pg_class'::regclass
  loop
    -- probably lock here can be safer, in safe (single user) maintainance mode
    -- it is not necessary
    execute format('lock table %s in exclusive mode', r.reloid::regclass);

    -- expect usual one sequnce per table
    execute format('select COALESCE(max(%I),0) from %s', r.colname, r.reloid::regclass)
       into max_val;

    -- set sequence
    perform setval(r.seqoid, max_val + 1);
  end loop;
end;
$$

Note: Using %s for table name or sequence name in format function is safe, because the cast from Oid type to regclass type generate safe string (schema is used when it is necessary every time, escaping is used when it is needed every time).

How to group the same values which is in sequence order

This is a gaps and islands problem. Here is one way to solve it using lag() and a cumulative sum():

select min(num) num, count(*) count_num
from (
    select t.*, sum(case when num = lag_num then 0 else 1 end) over(order by id) grp
    from (
        select t.*, lag(num) over(order by id) lag_num
        from #temp t
    ) t
) t
group by grp

Demo on DB Fiddlde:

num | count_num
--: | --------:
  1 |         3
  2 |         1
  1 |         1
  2 |         2
  3 |         3

How can I get all sequences bound with a table in PostgreSQL

try with:

python manage.py sqlsequencereset <appname>

and feed that to psql cli

HTH

---

Related Topics