1

Let's say I have the following table:

date       | name  | value
----------------------------
2020-09-01 | name1 | 10
2020-09-02 | name1 | 9
2020-09-03 | name1 | 12
2020-09-04 | name1 | 11
2020-09-05 | name1 | 11

I would like to identify names where the latest value >= 10 AND where over the last 5 days it has ever dropped below 10. In the example table above, name1 would be returned because the latest date has a value of 11 (which is > 10), and over the last 5 days it has dropped below 10 at least once.

Here is my SELECT statement, but it always returns zero rows:

SELECT
   name,
   count(value) as count
FROM table_name
WHERE 
   (date = @date AND value >= 10) AND
   date BETWEEN date_sub(@date, interval 5 day) AND @date AND value < 10
GROUP BY name
HAVING count < 5
ORDER BY name

I understand why it's failing, but I don't know what to change.

GMB
  • 216,147
  • 25
  • 84
  • 135
Rich
  • 7
  • 2

2 Answers2

0

In MySQL 8.0, you could use window functions and aggregation:

select name
from (
    select t.*, row_number() over(partition by name order by date desc) rn
    from mytable t
    where date >= @date - interval 5 day and date <= @date
) t
having max(case when rn = 1 then value end) >= 10 and min(value) <= 10
GMB
  • 216,147
  • 25
  • 84
  • 135
0

How about something like this:

SELECT Name, COUNT(*) AS Ct FROM
(SELECT A.*,B.mdate, 
       CASE WHEN A.date=B.mdate AND A.value >= 10 THEN 1 
            WHEN A.date >= B.mdate - INTERVAL 5 DAY AND A.date <> B.mdate AND A.value < 10 THEN 1
            ELSE 0 END AS Chk  
FROM table_name A 
JOIN (SELECT Name,MAX(DATE) AS mdate FROM table_name GROUP BY Name) B ON A.Name=B.Name 
HAVING Chk <> 0) V
GROUP BY Name 
HAVING Ct >= 2

Here's a fiddle for reference: https://www.db-fiddle.com/f/jX4GktCdTrUbqHBf7ZQwdr/0

And here's a breakdown of what the query above is doing.

  1. Joining table_name with a sub-query of the same table but with MAX(DATE) value for comparison.
  2. Using CASE function to check for your conditions; if matches with the conditions, it will return 1, if not, return 0. Added HAVING to exclude any 0 value from the CASE function.
  3. Turn the query to become a sub-query (assigned as V) and do a COUNT(*) over how many occurrence happen on the name then using HAVING again to get any name that have 2 or more occurrence.
FanoFN
  • 6,815
  • 2
  • 13
  • 33