Recent

Author Topic: SQL question - specific filtering  (Read 1178 times)

backprop

  • Full Member
  • ***
  • Posts: 101
SQL question - specific filtering
« on: July 17, 2024, 01:48:08 pm »
I'm not fluent in SQL, thus some uncommon question.

I have table as this, all strings:
Code: Pascal  [Select][+][-]
  1. Datetime        Level   Critical
  2.         Level1  Red
  3.         Level1  Red
  4.         Level1  Yellow
  5.         Level2  Green
  6.         Level2  Red
  7.         Level3  Green
  8.         Level3  Yellow
  9.         Level3  Green
  10. .           
  11.  

Thus are list of various warnings and critical states and during day should be created statistic as follows:
Code: Pascal  [Select][+][-]
  1.         Level1  3
  2.         Level2  3
  3.         Level3  Green 2
  4.         Level3  Yellow 1
  5.  

In short level 1 and 2 are not so critical warnings and should be just listed total number of it, but for all others should be also listed statistic of all states.

This can be done in one query or must be created new table from query, then filter each step?

That may be done in SQLite, for instance.

If not possible in one query, AFAIK, CREATE TABLE result AS .... is allowed in SQLite.
« Last Edit: July 17, 2024, 01:56:48 pm by backprop »

gidesa

  • Full Member
  • ***
  • Posts: 145
Re: SQL question - specific filtering
« Reply #1 on: July 17, 2024, 02:07:46 pm »
Use 3 distinct query and do Union on these.
Of course they must have the same output columns. So for level 1 and 2  third col. will be a constant, for example "All states".

dseligo

  • Hero Member
  • *****
  • Posts: 1412
Re: SQL question - specific filtering
« Reply #2 on: July 17, 2024, 02:31:05 pm »
You could also do it with subquery.

If you create table and data like this:
Code: MySQL  [Select][+][-]
  1. create table states (coldatetime datetime, collevel text, colcritical text);
  2. insert into states (collevel, colcritical) values ('Level1', 'Red');
  3. insert into states (collevel, colcritical) values ('Level1', 'Red');
  4. insert into states (collevel, colcritical) values ('Level1', 'Yellow');
  5. insert into states (collevel, colcritical) values ('Level2', 'Green');
  6. insert into states (collevel, colcritical) values ('Level2', 'Red');
  7. insert into states (collevel, colcritical) values ('Level3', 'Green');
  8. insert into states (collevel, colcritical) values ('Level3', 'Yellow');
  9. insert into states (collevel, colcritical) values ('Level3', 'Green');

Then this query:
Code: MySQL  [Select][+][-]
  1. select collevel, critical, count(*) as statistic
  2. (select collevel, case when collevel = 'Level3' then colcritical else '' end as critical
  3.  from states)
  4. group by collevel, critical

Will produce:
Code: Text  [Select][+][-]
  1. collevel  critical  statistic
  2. Level1              3
  3. Level2              2
  4. Level3    Green     2
  5. Level3    Yellow    1

Zvoni

  • Hero Member
  • *****
  • Posts: 2744
Re: SQL question - specific filtering
« Reply #3 on: July 17, 2024, 04:24:18 pm »
Use 3 distinct query and do Union on these.
Of course they must have the same output columns. So for level 1 and 2  third col. will be a constant, for example "All states".
Not 3!
2 are enough

Untested
Code: SQL  [Select][+][-]
  1. SELECT level, critical, COUNT(*) FROM MyTable WHERE level>='Level 3' GROUP BY level, critical
  2. UNION ALL
  3. SELECT level, '' AS Dummy, COUNT(*) FROM MyTable WHERE level<'Level 3' GROUP BY level, Dummy  
  4. ORDER BY 1
Note: The second Select doesn't work in all DBMS, because this is a GROUP BY SomeAlias
MySQL and Postgres can do it, others don't

Everything said: What's the Target DBMS? MySQL? SQLite?

and upfront: The way i would do it is definitely different (Keyword: CTE's pre-building the filter-criteria, so in the end it boils down to a simple JOIN)
though it's very close to dseligo's Solution
« Last Edit: July 17, 2024, 04:32:16 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

backprop

  • Full Member
  • ***
  • Posts: 101
Re: SQL question - specific filtering
« Reply #4 on: July 17, 2024, 05:28:54 pm »
Code: [Select]
drop table if exists states;

create table states (
  datetime datetime,
  level text,
  critical text
);

insert into states (level, critical) values ('Level1', 'Red');
insert into states (level, critical) values ('Level1', 'Red');
insert into states (level, critical) values ('Level1', 'Yellow');
insert into states (level, critical) values ('Level2', 'Green');
insert into states (level, critical) values ('Level2', 'Red');
insert into states (level, critical) values ('Level3', 'Green');
insert into states (level, critical) values ('Level3', 'Yellow');
insert into states (level, critical) values ('Level3', 'Green');

create index if not exists idx_level on states (level);
create index if not exists idx_critical on states (critical);


Code: [Select]
select level, critical, count(*) as statistic
from
(
  select level,

  case when
    (level <> 'Level1') and (level <> 'Level2')
    then critical
    else ''
  end as critical

from states)

group by level, critical;


or

Code: [Select]
select level, '' as critical, count(*) as count from states
where
  (level='Level1') or (level='Level2')
group by 1

union all

select level, critical, count(*) as count from states
where
  (level <> 'Level1') and (level <> 'Level2')
group by 1, 2

order by 1,2;

Level 1,2,etc are just aliases for proper strings, thus it is not possible to use operators as "<" or ">" on strings here. Both fields have indices.

Thanks to both of you.

Furthermore, what would be faster? I believe nested select is always slower, but all depends on internal engine optimization
« Last Edit: July 17, 2024, 05:43:21 pm by backprop »

backprop

  • Full Member
  • ***
  • Posts: 101
Re: SQL question - specific filtering
« Reply #5 on: July 17, 2024, 06:07:26 pm »
Let also say that I have list of these warning and weight of it. For instance:

Code: [Select]

level, weight

Level1 1
Level2 5
Level3 Green 1
Level3 Yellow 50
Level3 Red 100

Level4 Green 1
Level4 Yellow 500
Level4 Red 1000
...

And that I also want to have weight value which could be multiplied with count as well to show more clearly the risk. Well, then could be used JOIN, but also is possible that raw data contain new string, not added yet. IDs could be added as well, but all that making life more complicated...
« Last Edit: July 17, 2024, 06:10:07 pm by backprop »

Zvoni

  • Hero Member
  • *****
  • Posts: 2744
Re: SQL question - specific filtering
« Reply #6 on: July 18, 2024, 08:13:33 am »
Interesting issue.

Do you have some concrete data-samples?

"This is what i have, and that's what i want"
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 2744
Re: SQL question - specific filtering
« Reply #7 on: July 23, 2024, 01:06:21 pm »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018