How to make Mysql variables work in a query

Question

I've been struggling for a while now with attempting to generate code for automatic aggregations in my mysql/mariadb database. The method That i'm currently trying uses variables. I will admit in advance I'm not a database expert by any means. I'm totally self taught, and have been struggling to find adequate resources for this particular problem. Ive included simplified examples below, Oh and i'm using mariadb 10.1.

This code should work in mysql 5.6 as well as mariadb 10.0+, I have tested it on 10.1 and it works. Here is my Table: and SQL FIDDLE <- doesn't work for some reason. Probably the dynamic columns. I'll leave it in case someone knows why.

CREATE TABLE data_points
(
  id          BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  device_id   INTEGER,
  dtime       DATETIME,
  sf          INTEGER(11), -- sample frequency or interval
  agg         INTEGER(11), -- aggregation type, actually a fk
  data_point BLOB,
  PRIMARY KEY (id),
  UNIQUE (device_id, dtime, sf, agg)
);

Lets insert some data:

INSERT INTO data_points
    (device_id, dtime, sf, agg, data_point)
VALUES
    (1, '2015-01-02 12:00:00', 1, 60, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 13:00:00', 1, 60, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 14:00:00', 1, 60, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 15:00:00', 1, 60, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 16:00:00', 1, 60, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45));

So up to this point everything works just fine. What i'm trying to do is perform aggregations over different time periods, my lowest grain period is 60 seconds. Here is where I have issues, Its probably something obvious.

SELECT
  @dp_dtime := MAX(dtime),
  @dp_aa := MIN(ROUND(COLUMN_GET(data_point, 'aa' AS DOUBLE), 4)),
  @dp_ab := MIN(ROUND(COLUMN_GET(data_point, 'ab' AS DOUBLE), 4)),
  @dp_ac := MIN(ROUND(COLUMN_GET(data_point, 'ac' AS DOUBLE), 4))
FROM data_points
WHERE
  device_id = 1 AND
  dtime BETWEEN '2015/01/02 12:00:00' AND '2015/01/17 23:05:00' AND
  sf = 60 AND
  agg = 1;

INSERT INTO data_points
(device_id, dtime, sf, agg, data_point)
VALUES (8, @dp_dtime, 300, 2, COLUMN_CREATE('aa', @dp_aa, 'ab', @dp_ab, 'ac', @dp_ac));

This ends up creating another row with NULL everywhere a variable was in the statement.

select @dp_dtime, @dp_aa, @dp_ab, @pd_ac;
-- This results in NULL, NULL, NULL, NULL

At this point I'm pretty sure i'm doing something wrong with the variables. It's Late, 14 hour day. Am I even close? Is there a better/easier way? Any help would be greatly appreciated.

EDIT: In my real use case the number of columns is dependent on the type of device were doing an aggregation for. Columns are excel style 'aa' through 'zz' possible. although the max I've seen is about 150 cols wide. This may sound like a bad design, but the performance is surprising, I can't tell the difference between these dynamic columns and actual columns. (at least as long as you don't need to index on them)

Answer 1

Possibly a simple typo: I see @_dtime.

In the UNIQUE index, put dtime last; it will make the queries faster. Mini index lesson: All = columns should come first in a composite index, in any order (cardinality makes virtually no difference). Then you can put one 'range' (dtime). Any columns after a range are not used for filtering. See my cookbook.

Get rid of id and promote the UNIQUE index to PRIMARY KEY; it will make the queries still faster. Mini index lesson: Secondary keys (such as your UNIQUE) requires bouncing between the key and the data. The PRIMARY KEY is clustered with the data (in InnoDB), thereby avoiding the bouncing. Instead a 'range scan' over the PK is a range over the table.

Answer 2

Try the following queries.

SQL:

CREATE TABLE data_points
(
  id          BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  device_id   INTEGER,
  dtime       DATETIME,
  sf          INTEGER(11), -- sample frequency or interval
  agg         INTEGER(11), -- aggregation type, actually a fk
  data_point BLOB,
  UNIQUE (device_id, dtime, sf, agg)
);
INSERT INTO data_points
    (device_id, dtime, sf, agg, data_point)
VALUES
    (1, '2015-01-02 12:00:00', 1, 1, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 13:00:00', 1, 1, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 14:00:00', 1, 1, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 15:00:00', 1, 1, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45)),
    (1, '2015-01-02 16:00:00', 1, 1, COLUMN_CREATE('aa', 12, 'ab', 34, 'ac', 45));
select * from data_points;
SELECT
  @dp_dtime := MAX(dtime) as dp_dtime,
  @dp_aa := MIN(ROUND(COLUMN_GET(data_point, 'aa' AS DOUBLE), 4)) as dp_aa,
  @dp_ab := MIN(ROUND(COLUMN_GET(data_point, 'ab' AS DOUBLE), 4)) as dp_ab,
  @dp_ac := MIN(ROUND(COLUMN_GET(data_point, 'ac' AS DOUBLE), 4)) as dp_ac
FROM data_points
WHERE
  device_id = 1 AND
  dtime BETWEEN '2015/01/02 12:00:00' AND '2015/1/17 23:05:00' AND
  sf = 1 AND
  agg = 1;
INSERT INTO data_points
(device_id, dtime, sf, agg, data_point)
VALUES (8, @dp_dtime, 300, 2, COLUMN_CREATE('aa', @dp_aa, 'ab', @dp_ab, 'ac', @dp_ac));
select * from data_points;

Output:

mysql> select * from data_points;
+----+-----------+---------------------+------+------+----------------------------+
| id | device_id | dtime               | sf   | agg  | data_point                 |
+----+-----------+---------------------+------+------+----------------------------+
|  1 |         1 | 2015-01-02 12:00:00 |    1 |    1 |            aaabacDZ |
|  2 |         1 | 2015-01-02 13:00:00 |    1 |    1 |            aaabacDZ |
|  3 |         1 | 2015-01-02 14:00:00 |    1 |    1 |            aaabacDZ |
|  4 |         1 | 2015-01-02 15:00:00 |    1 |    1 |            aaabacDZ |
|  5 |         1 | 2015-01-02 16:00:00 |    1 |    1 |            aaabacDZ |
+----+-----------+---------------------+------+------+----------------------------+
5 rows in set (0.00 sec)

mysql> SELECT
    ->   @dp_dtime := MAX(dtime) as dp_dtime,
    ->   @dp_aa := MIN(ROUND(COLUMN_GET(data_point, 'aa' AS DOUBLE), 4)) as dp_aa,
    ->   @dp_ab := MIN(ROUND(COLUMN_GET(data_point, 'ab' AS DOUBLE), 4)) as dp_ab,
    ->   @dp_ac := MIN(ROUND(COLUMN_GET(data_point, 'ac' AS DOUBLE), 4)) as dp_ac
    -> FROM data_points
    -> WHERE
    ->   device_id = 1 AND
    ->   dtime BETWEEN '2015/01/02 12:00:00' AND '2015/1/17 23:05:00' AND
    ->   sf = 1 AND
    ->   agg = 1;
+---------------------+---------+---------+---------+
| dp_dtime            | dp_aa   | dp_ab   | dp_ac   |
+---------------------+---------+---------+---------+
| 2015-01-02 16:00:00 | 12.0000 | 34.0000 | 45.0000 |
+---------------------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO data_points
    -> (device_id, dtime, sf, agg, data_point)
    -> VALUES (8, @dp_dtime, 300, 2, COLUMN_CREATE('aa', @dp_aa, 'ab', @dp_ab, 'ac', @dp_ac));
Query OK, 1 row affected (0.00 sec)

mysql> select * from data_points;
+----+-----------+---------------------+------+------+-------------------------------------------------+
| id | device_id | dtime               | sf   | agg  | data_point                                      |
+----+-----------+---------------------+------+------+-------------------------------------------------+
|  1 |         1 | 2015-01-02 12:00:00 |    1 |    1 |            aaabacDZ                      |
|  2 |         1 | 2015-01-02 13:00:00 |    1 |    1 |            aaabacDZ                      |
|  3 |         1 | 2015-01-02 14:00:00 |    1 |    1 |            aaabacDZ                      |
|  4 |         1 | 2015-01-02 15:00:00 |    1 |    1 |            aaabacDZ                      |
|  5 |         1 | 2015-01-02 16:00:00 |    1 |    1 |            aaabacDZ                      |
|  6 |         8 | 2015-01-02 16:00:00 |  300 |    2 |       ▒  aaabac      (@      A@     ▒F@ |
+----+-----------+---------------------+------+------+-------------------------------------------------+
6 rows in set (0.00 sec)