Mysql – User variable in nested subquery not getting picked up – in complex sort of 2 tables with gaps

MySQLmysql-5.5subquery

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a subquery. In this subquery I need to generate a number by doing a lookup on the first table and modifying the result.

More specifically, I have a table A (id, date), and table B (id, weekday) and I need to return a date for each row of B where it is the next date occurring on the same weekday where such a date does not occur in A, all starting from a specific minimum date, where entries in table A are all higher (ie, later) than that date. Table B is sorted by staggered weekday (First Monday, First Tuesday… Second Monday, Second Thursday..) and then these get "slotted in" (by assigning "date") where that date in A does not occur. So if the two tables look something like:

   A   
id  | date
a-a   2014-11-11
a-b   2014-11-13
a-c   2014-11-18

   B
id  | weekday
b-a   Tuesday
b-b   Tuesday
b-c   Wednesday

Then the resulting sort (if start date is 2014-11-10, a Monday) would be:

  sorted
id  | date
a-a   2014-11-11
b-c   2014-11-12  (Wednesday)
a-b   2014-11-13
a-c   2014-11-18
b-a   2014-11-25  (Tuesday)
b-b   2014-12-02  (Tuesday)

I have been working on this query for a while now, and almost had it done, but then I couldn't find a way to pass the "start date" to the subquery that allocates dates to table B. The docs say that

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query.

So I reimplemented the subquery to move the variable to a WHERE clause, using JOINs, but this still didn't seem to help, since I couldn't figure out how to return just the single row needed without an outer query to do that. So here is what I have, in the hopes someone can help me figure this out:

   SELECT
    @next_date as next_date,
    @tmp_i := CASE w.weekday + 0
        WHEN 0 THEN @idxw_U:=@idxw_U+1
        WHEN 1 THEN @idxw_M:=@idxw_M+1
        WHEN 2 THEN @idxw_T:=@idxw_T+1
        WHEN 3 THEN @idxw_W:=@idxw_W+1
        WHEN 4 THEN @idxw_R:=@idxw_R+1
        WHEN 5 THEN @idxw_F:=@idxw_F+1
        WHEN 6 THEN @idxw_S:=@idxw_S+1
    END as idxW,
    @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7  as idxw_offset,
    @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7  as idxW_corr,
    w.weekday as weekday,
    @dayofweek:= w.weekday+0 as dweekday,
    @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,

    @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date),
        IFNULL(
            (
            SELECT next_slot FROM (
                SELECT 
                    -- DAYOFWEEK(d.date) as weekdaynum,
                    -- dayname(d.date) as weekday,
                    -- d.date,
                    -- MIN(d2.date) as min_d2,
                    DATE_ADD(MIN(d2.date), INTERVAL 1 WEEK) as next_slot,
                    -- DATEDIFF(d2.date,  d.date) as datediff,
                    -- DATEDIFF(d2.date,  d.date) DIV 7 as weeksdiff,
                    -- DATEDIFF(MIN(d3.date),  d2.date) DIV 7 as nextdiff

                     FROM  t_date as d
                     JOIN  t_date as d2
                     JOIN  t_date as d3
                    WHERE d.date >= @TMP_date
                    and DAYOFWEEK(@TMP_date) = DAYOFWEEK(d.date) =  DAYOFWEEK(d2.date)
                         = DAYOFWEEK(d3.date)
                    and d2.date > d.date
                        and d3.date > d2.date
                    and DATEDIFF(d2.date,  d.date) = 7
                    GROUP BY d.date
                    ORDER BY d.date ASC
                ) as t
             -- these 3 lines below are the ones I need to figure out how to accomplish without an outer query
             -- ie, find the first (lowest date) result that has a nextdiff greater than 1 (a gap of more than 1 week between results)
            WHERE nextdiff >1
            ORDER BY date ASC
            LIMIT 1
            )
            , CONCAT('dow:  ',@dayofweek,'  ',@TMP_date) )
            -- , 1) -- commented this out while debugging, debug output is line above; in final, if return is null, that means offset is one week
        ,
        0
    ) as clash_offset,

    @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,
    w.extra
FROM `t_weekday` as w
JOIN (SELECT
    @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
    @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) 
) as varrs
ORDER BY date DESC, weekday, id ASC

@TMP_date is the starting (minimum) date. The subquery is the one inside the IFNULL, and tested outside of this, as its own query, it works perfectly, when specifying a literal value (or setting the variable in a JOIN()).

As a test, I did:

   SELECT
    @next_date as next_date,
    @tmp_i := CASE w.weekday + 0
        WHEN 0 THEN @idxw_U:=@idxw_U+1
        WHEN 1 THEN @idxw_M:=@idxw_M+1
        WHEN 2 THEN @idxw_T:=@idxw_T+1
        WHEN 3 THEN @idxw_W:=@idxw_W+1
        WHEN 4 THEN @idxw_R:=@idxw_R+1
        WHEN 5 THEN @idxw_F:=@idxw_F+1
        WHEN 6 THEN @idxw_S:=@idxw_S+1
    END as idxW,
    @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7  as idxw_offset,
    @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7  as idxW_corr,
    w.weekday as weekday,
    @dayofweek:= w.weekday+0 as dweekday,
    @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,

    @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date),
        IFNULL(:= IF((SELECT 1 FROM t_date WHERE show_after = @TMP_show_after),
    IFNULL(
        (
            -- SELECT f FROM ( -- uncommenting this line and the ftmp one results in NULL, with them commented out, @TMP_date is picked up no problem
               SELECT  @TMP_date as f
               LIMIT 1
               -- ) as ftmp
         )
           , CONCAT('dow:  ',@dayofweek,'  ',@TMP_show_after) )
           -- , 1)
        ,
        0
    ) as clash_offset,

    @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,
    w.extra
FROM `t_weekday` as w
JOIN (SELECT
    @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
    @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) 
) as varrs
ORDER BY date DESC, weekday, id ASC

Which confirms what the docs say. However, I don't know how to modify my query (the first one) to get the result I want.

Edit:
I found an error in the logic, I need to return the highest result (date) in a series of nextdiffs if all return 1, or the first higher-than-1 result. It currently only would return the latter. So that needs fixing too.

Best Answer

Ok, I solved it. I could not solve the actual problem of using the user variable nested in the subquery, as I think there is simply no way to do this (please comment, or answer if there is), but this is how I solved it, in case anyone is curious:

SELECT @idx:=@idx+1 as idx,
    date, weekday, id, added_on
    , from_table
FROM (
    SELECT  show_after,
        DAYNAME(date) as weekday,
        id,
        added_on,
        'date' as from_table
        FROM `t_date` as d

    UNION

    SELECT show_after,
        DAYNAME(date) as weekday,
        id,
        added_on,
        'weekday' as from_table
        FROM (
        SELECT
            @next_date as next_date,
            @tmp_i := CASE w.weekday + 0
                WHEN 0 THEN @idxw_U:=@idxw_U+1
                WHEN 1 THEN @idxw_M:=@idxw_M+1
                WHEN 2 THEN @idxw_T:=@idxw_T+1
                WHEN 3 THEN @idxw_W:=@idxw_W+1
                WHEN 4 THEN @idxw_R:=@idxw_R+1
                WHEN 5 THEN @idxw_F:=@idxw_F+1
                WHEN 6 THEN @idxw_S:=@idxw_S+1
            END as idxW,
            @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7  as idxW_corr,
            w.weekday as weekday,
            @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,

            @clash_offset:= IF( (SELECT 1 FROM t_date WHERE date = @TMP_date),
                DATEDIFF(
                    ( SELECT 
                        @next_slot:= DATE_ADD(d.date, INTERVAL 1 WEEK) as next_slot
                            FROM  t_date as d
                        WHERE d.date >= @TMP_date
                        and DAYOFWEEK(d.date) =  DAYOFWEEK(@TMP_date)
                        and NOT EXISTS (SELECT  date FROM t_date as dx WHERE date = DATE_ADD(d.date, INTERVAL 1 WEEK))
                        LIMIT 1
                    ),
                    @TMP_date
                    ) DIV 7
                , 0
                ) as clash_offset,

            @NEW_show_after:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,

            w.id,
            added_on
            FROM `t_weekday` as w
            JOIN (SELECT
                @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
                @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) 
            ) as varrs
            ORDER BY date, weekday, added_on ASC
        ) as weekday_calc
) as main

JOIN (SELECT 
    @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY)  as date_slot
    , @idx:=0
    ) as main_vars
ORDER BY date

So, in case you missed it, the clue was to use NOT EXISTS with a subquery in the WHERE clause to detect gaps, which simplified the entire approach.

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why

Best Answer

Related Solutions

Mysql – Rolling count of total transactions over time

Mysql – query records to fetch by time stamp

SUGGESTION #1

SUGGESTION #2

EPILOGUE

Related Question