CODEWITHSUNDEEP
mysqlsql-servergaps-and-islands
Rav
Rav

Reputation: 1387

Get recent count from a column

I would like to get the count of a column based on its recent status.

Please see table structure below:

id | visible |      date
1  |     1   |   2021-07-22
2  |     1   |   2021-07-23
3  |     0   |   2021-07-24
4  |     1   |   2021-07-25
5  |     0   |   2021-07-26
6  |     0   |   2021-07-27

For example, if I query

SELECT COUNT(visible) AS latest_not_visible WHERE date = '2021-07-26'

then it should return

latest_not_visible
      1

Since it only counts that date as not visible, it disregarded the count on 07/24 since 07/25 is visible

But if I query

SELECT COUNT(visible) AS latest_not_visible WHERE date = '2021-07-27'



latest_not_visible
      2

since 07/26 and 07/27 are both non-visible and no date in between is visible

I already had the solution to the problem, but I would need help in optimizing this function:

 IIF(datediff
        (day,
        (SELECT MAX(date) FROM t1 WHERE (visible = 0 OR visible = '-1' OR visible = '-3') AND item_id = vp.item_id AND [date] <= vp.date),
        (SELECT MAX(date) FROM t1 WHERE visible = 1 AND item_id = vp.item_id AND [date] <= vp.date)) IS NULL 
            OR datediff(day,
                        (SELECT MAX(date) FROM t1 WHERE (visible = 0 OR visible = '-1' OR visible = '-3') AND item_id = vp.item_id AND [date] <= vp.date),
                        (SELECT MAX(date) FROM t1 WHERE visible = 1 AND item_id = vp.item_id AND [date] <= vp.date)) < 0, 
 (SELECT COUNT(1) FROM t1 WHERE (visible = 0 OR visible = '-1' OR visible = '-3') AND item_id = vp.item_id AND [date] <= vp.date), 0) 
 AS times_not_visible,
 
 table vp is the original table same with t1

Upvotes: 0

Views: 123

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use window functions:

select count(*)
from (select t.*,
             max(case when visible = 1 then date end) over (order by date) as max_visible_date
      from t
      where date <= '2021-07-26'    -- or whatever
     ) t
where visible = 0 and
      (date > max_visible_date or max_visible_date is null);

Note that this version only mentions the date once. It also works for if there are no rows with visible = 1, and it works on any number of rows.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272006

Find the latest visible date earlier than the given date and count all rows in between those two dates:

SET @dt = '2021-07-25';
SELECT COUNT(*)
FROM t
WHERE date <= (SELECT date FROM t WHERE date = @dt AND visible = 0)
AND   date >  (SELECT date FROM t WHERE date < @dt AND visible = 1 ORDER BY date DESC LIMIT 1)

SQL Fiddle

Upvotes: 1

forpas
forpas

Reputation: 164064

A solution with GROUP_CONCAT():

SELECT CHAR_LENGTH(visible) - CHAR_LENGTH(TRIM(TRAILING '0' FROM visible)) latest_not_visible 
FROM (
  SELECT GROUP_CONCAT(visible ORDER BY date SEPARATOR '') visible
  FROM tablename
  WHERE date <= ?
) t

Change ? to the date you want.

See the demo.

Upvotes: 0

Dazz Knowles
Dazz Knowles

Reputation: 534

Rather than counting them, you're better to calculate the number of days between the last visible and not visible on or before that date. So something like this...

SELECT DATEDIFF(
   SELECT MAX(date) FROM YourTable WHERE visible = 0 AND date <= '2021-07-27'),
   SELECT MAX(date) FROM YourTable WHERE visible = 1 AND date <= '2021-07-27')
) as latest_not_visible;

Upvotes: 1

Related Questions