Skip to content

Instantly share code, notes, and snippets.

@jonathanvx
Last active December 22, 2016 12:52
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jonathanvx/56dfbadaf0a51f4eddfe8b3eecfbbbec to your computer and use it in GitHub Desktop.
Save jonathanvx/56dfbadaf0a51f4eddfe8b3eecfbbbec to your computer and use it in GitHub Desktop.
/*
'year' and 'month' columns are in seperate integer columns.
This creates an issue when wanting reports between two different dates.
How can we fix this? One solution, a generated column with an index on it.
*/
MBP@landregistry> select * from summary order by total_price desc limit 3;
+----------+------+-------+------------+----------------+--------------+-------------+
| postcode | year | month | county | district | total_price | houses_sold |
+----------+------+-------+------------+----------------+--------------+-------------+
| W1T | 2015 | 7 | CAMDEN | GREATER LONDON | 367075000.00 | 7 |
| SS14 | 2014 | 1 | BASILDON | ESSEX | 356644505.00 | 27 |
| UB3 | 2013 | 12 | HILLINGDON | GREATER LONDON | 348528830.00 | 39 |
+----------+------+-------+------------+----------------+--------------+-------------+
3 rows in set (0.46 sec)
-- Develop a function to join data from the two columns and convert into a date
MBP@landregistry> select date(concat_ws('-',year,month,'01')) from summary limit 3;
+--------------------------------------+
| date(concat_ws('-',year,month,'01')) |
+--------------------------------------+
| 1995-01-01 |
| 1995-01-01 |
| 1995-01-01 |
+--------------------------------------+
3 rows in set (0.01 sec)
-- Use the function you developed and ass it as a generated column to the table
MBP@landregistry> alter table summary add vdate date GENERATED ALWAYS
AS (date(concat_ws('-',year,month,'01'))) STORED;
Query OK, 890950 rows affected (4.25 sec)
Records: 890950 Duplicates: 0 Warnings: 0
-- Add an index on that column (could have been done in the previous statement as well)
MBP@landregistry> alter table summary add index vdate(vdate);
Query OK, 0 rows affected (1.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- Test to see if it works.
MBP@landregistry> select sum(houses_sold) from summary
where vdate between '2010-01-01' and '2010-12-01';
+------------------+
| sum(houses_sold) |
+------------------+
| 662817 |
+------------------+
1 row in set (0.26 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment