How to put zero or null in a SQL query of a date range, for those whose value does not exist?

3

Formulate the question was a bit difficult, but I'll try to be as explicit as possible using an example to set the question straight.

There are the following data in my table:

Thepurposeistoexecuteaquerysql(theDBMSusedisMySQL)inthedaterangebetween2014-09-01until2014-09-10andhavethefollowingresult:

What I already have:

To be honest, I have a lot of no idea how to do this, so far I have solved this in the control of my application, an algorithm like:

  • Select the data in the DB, by means of between of the desired dates and save to a list;
  • For each day (or time) existing in the range of the desired date, save to an auxiliary variable (an instance of Calendar in Java);
  • Check if there is an element with this date in the list obtained in step 1, if not, add an element in the resulting list with that date, but zero in the quantity field;
  • Is there any way to do this already in the query in sql ? The way I do now is not the most efficient.

        
    asked by anonymous 25.09.2014 / 13:36

    3 answers

    3

    You can use a generator for no intermediate tables: / p>

    SELECT
       DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) AS diaDoAno
    FROM
       ( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
       ( SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2,
       ( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) t3
    WHERE
       YEAR( DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) ) = 2014
    

    The query looks great, but the performance is excellent.

    Operation:

  • Each subquery generates the numbers 0 through 9, except the last one, which generates numbers from 0 to 3;
  • The formula d1+d2*10+d3*100 transforms the outputs of the 3 subqueries into a number of 0 to 399
  • With DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) we've turned this number into a date.
  • To limit to the days of the year in question, we use the same formula that generates the date, to ensure that we are no longer than one year (remembering that the number of days varies in leap years).

  • How to use:

    As described in other answers, just use JOIN with the desired table.

    SELECT
       DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) AS dia_do_ano.
       contador,
       quantidade
    FROM
       ( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
       ( SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2,
       ( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) t3
    LEFT JOIN
        sua_tabela ON dia_do_ano = data_tempo
    WHERE
        YEAR( DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) ) = 2014
    
        
    25.09.2014 / 17:07
    1

    Cold. As the Wakin suggested and as I was writing.

    Make a date table, and from there a LEFT JOIN with your table.

    Here are two links to suggestions for creating this table:

    Generate Table Dates 1

    Generate Table Dates 2

    For null values, just add COUNT to your IFNULL() and your problem will be solved.

        
    25.09.2014 / 14:29
    1

    You can create a PROCEDURE by creating a temporary table before running the query, for example:

    DELIMITER //
    CREATE PROCEDURE TempDateTable(data_ini DATE, data_fim DATE)
    
    BEGIN
        DECLARE v_curdate DATE;
        SET @v_curdate = data_ini;
        START TRANSACTION;
        DROP TABLE IF EXISTS tempdatetable;
    
        CREATE TEMPORARY TABLE tempdatetable  (
            data_tempo DATE NOT NULL PRIMARY KEY
        );
    
      WHILE @v_curdate <= data_fim DO
        INSERT INTO 'TempDateTable'('data_tempo') VALUES (@v_curdate);
        SET @v_curdate = ADDDATE(@v_curdate, INTERVAL 1 DAY);
      END WHILE;
      COMMIT;
    END//
    DELIMITER ;
    

    Data

    --------------------------------------
    | data_tempo | contador | quantidade |
    --------------------------------------
    | 2014-09-01 | CDR_SUC  | 256        |
    | 2014-09-04 | CDR_SUC  | 258        |
    | 2014-09-05 | CDR_SUC  | 195        |
    | 2014-09-06 | CDR_SUC  | 100        |
    | 2014-09-10 | CDR_SUC  | 317        |
    --------------------------------------
    

    Running Procedure

    CALL 'databasetest'.'TempDateTable'('2014-09-01', '2014-09-10');
    

    Conducting the query

    SELECT tmp.data_tempo, test.contador, IFNULL(test.quantidade,0) 
    FROM tempdatetable as tmp 
        LEFT JOIN  testedados as  test ON DATE(test.data_tempo) = DATE(tmp.data_tempo);
    

    Result:

    --------------------------------------
    | data_tempo | contador | quantidade |
    --------------------------------------
    | 2014-09-01 | CDR_SUC  | 256        |
    | 2014-09-02 | NULL     | 0          |
    | 2014-09-03 | NULL     | 0          |
    | 2014-09-04 | CDR_SUC  | 258        |
    | 2014-09-05 | CDR_SUC  | 195        |
    | 2014-09-06 | CDR_SUC  | 100        |
    | 2014-09-07 | NULL     | 0          |
    | 2014-09-08 | NULL     | 0          |
    | 2014-09-09 | NULL     | 0          |
    | 2014-09-10 | CDR_SUC  | 317        |
    --------------------------------------
    
        
    25.09.2014 / 16:19