Refactor with FILTERS and same condition in WHERE

Question

Any help for refactoring this query? Thank you

SELECT filter(count(*), WHERE action='INIT_PMS' AND error IS TRUE AND manualSelected IS NOT NULL) AS 'Error',
  filter(count(*), WHERE action='INIT_PMS' AND error IS FALSE AND manualSelected IS NOT NULL) AS 'No Error',
  filter(count(*), WHERE action='INIT_PMS' AND error IS NOT NULL AND manualSelected IS NOT NULL) AS 'No Value'
FROM MyDatabase SINCE 4 MONTHS AGO TIMESERIES

It's in New Relic. My question is if it's possible to refactor the 3 filters — McKencie, May 24 '22 at 10:05
Please provide a few rows of sample data and the expected result. — The Impaler, May 24 '22 at 12:56
The `FILTER(COUNT(*)...)` is easy to rephrase using `COUNT(CASE...)`. However, you'll need to explain what `SINCE 4 MONTHS AGO TIMESERIES` mea.s — The Impaler, May 24 '22 at 12:57

score 0 · Answer 1 · answered Jun 01 '22 at 21:17

You could just remove the duplicate filters from each filter() clause and add it them as generic filters after the FROM like this:

SELECT filter(count(*), WHERE error IS TRUE) AS 'Error',
  filter(count(*), WHERE error IS FALSE) AS 'No Error',
  filter(count(*), WHERE error IS NOT NULL) AS 'No Value'
FROM MyDatabase where action='INIT_PMS' AND manualSelected IS NOT NULL SINCE 4 MONTHS AGO TIMESERIES

..or you probably could use facet cases() if you don't want many SELECT options:

SELECT count(*) FROM MyDatabase where action='INIT_PMS' AND manualSelected IS NOT NULL facet cases(where error IS TRUE as 'Error', where error IS FALSE as 'No Error', where error IS NOT NULL as 'No Value') SINCE 4 MONTHS AGO TIMESERIES

Refactor with FILTERS and same condition in WHERE

1 Answers1