You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Select
firstname,LastName,Count(*)
From myTable
Group by firstname,LastName
HAVINGCount(*)>1GROUP BY combines rows where the named values are the same.
HAVING removes groups that do not meet the condition.
The above query will list the first and last names, along with a count of duplicates for all first/last names that actually have duplicates.
CREATETABLECommentable (
id SERIALPRIMARY KEY
);
CREATETABLEComments (
comment_id PRIMARY KEY, -- can be auto generated
foreign_id INTNOT NULL,
...
FOREIGN KEY (foreign_id) REFERENCES Commentable(id)
);
CREATETABLEBlogPosts (
blogpost_id INTPRIMARY KEY, -- notice this is not auto-generated
...
FOREIGN KEY (blogpost_id) REFERENCES Commentable(id)
);
CREATETABLEUserPictures (
userpicture_id INTPRIMARY KEY, -- notice this is not auto-generated
...
FOREIGN KEY (userpicture_id) REFERENCES Commentable(id)
);
Before you can insert a row into BlogPosts or UserPictures, you must insert a new row to Commentable to generate a new pseudokey id. Then you can use that generated id as you insert the content to the respective subtype table. NOT tested but https://stackoverflow.com/a/1665673/3484824 shows the possibility to use triggers to automate this process.
DELIMITER //DROPTRIGGER IF EXISTS ins_appointed//CREATETRIGGERins_appointed BEFORE INSERT ON`members:appointed`
FOR EACH ROW BEGININSERT INTO`members:abstract` (`type`) VALUES ('appointed');
SETNEW.id= LAST_INSERT_ID();
END; //
DELIMITER ;
For the past few days, I have been writing a lot of SQL queries to query SQLite databases. I had to extract data for reporting purposes from SQLite databases where simple SELECT-FROM-WHERE queries weren’t enough. From this experience, I learnt few tricks that I am sure some of you will be interested in. So today, I will list it all in this blog post.
This post is composed by six parts:
Use the built in date functions
Cast your string to integer with CAST
Transpose a table using GROUP BY, CASE and Aggregate functions
Concatenate value with ||
Attach databases to JOIN on tables from different databases
Improve the performance of your query with EXPLAIN QUERY PLAN
The parts aren’t related with one another.
1. Use the built in date functions
strftime is the main function for datetime manipulation. It takes a format, a string date and some modifiers. Here are the format extracted from https://www.sqlite.org/lang_datefunc.html.
%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
Using strftime, you can get the day of the month, the month number, the year and other information out of a date.
The modifiers are used to modify the date passed as argument.
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc
Very useful when you need to add or substract from the date, for example you can use +2 months to have the date in two month time from the date passed as argument.
If you are saving your datetime as ticks from DateTime.Now.Ticks, you can use unixepoch with the following calculation.
SELECTdate(timestamp/10000000-62135596800, 'unixepoch')
--------------62135596800 is the number of second from01/01/0001 till 01/01/1970/10000000 is the conversion from ticks to seconds
2. Cast your string to integer with CAST
When you handle string values it is sometime required to cast those to integer (or to another type). For example when you get a month from a date(...), it returns as a string. In order to perform a comparaison, it is necessary to cast it.
3. Transpose a table using GROUP BY, CASE and Aggregate functions
In one the database I worked on, the values were stored in three columns id, key and value. This table gathers all the data sent from a form from our app. id is the identifier of the form, key is the key of the field and value is the value of the field.
The table is designed this way to handle the dynamic nature of the forms. Fields can be added or removed every day, depending on client requirements, so it would not be possible to use the value of keys as table columns.
Storing the values this way makes it difficult to query directly. What we need to do is to transpose the tabe.
For example if you have a table like that:
id key value
-- --- -----1 amount 10.01date2016-03-011 name Kim
2 amount 32.02date2016-03-022 name Sam
3 amount 12.53date2016-03-033 name Tom
To work with this table we need to transpose it by taking the keys and transform it to columns.
id | key | value --> id | date | name | amount
In order to do that we need to GROUP BY the id. We can visualize the GROUP BY like so:
SELECT (some aggregate function) FROM forms GROUP BY id
11 amount 10.01date2016-03-011 name Kim
22 amount 32.02date2016-03-022 name Sam
33 amount 12.53date2016-03-033 name Tom
avg(X) - calculate the average
count(X) - count the number of non nullvaluescount(*) - count the number of rows
group_concat(X) - concat the string values
group_concat(X,Y) - concat the string values using Y as seperator
max(X) - keep the max of all valuesmin(X) - keep the min of all valuessum(X) - sum all values (SQL implementation)
total(X) - sum all values (SQLite implementation)
We can use max(...) combined with CASE to select the correct value in the grouping for each column. CASE is the if else of SQL. For example to select to extract the name as a column, we would do the following:
SELECTmax(CASE WHEN key ='name' THEN value END) as name FROM forms GROUP BY id;
name
----
Kim
Sam
Tom
CASE WHEN key = 'name' THEN value END would take the value if the key = 'name' else it would return NULL. max(...) returns the max value which is the value where the key = name since all other values are set to NULL. We then apply the same pattern for the other columns:
SELECT
id,
max(CASE WHEN key ='name' THEN value END) AS name,
max(CASE WHEN key ='date' THEN value END) ASdate,
sum(CASE WHEN key ='amount' THEN value END) AS amount
FROM forms
GROUP BY id
id name date amount
-- ---- ---- -----1 Kim 2016-03-0110.02 Sam 2016-03-0232.03 Tom 2016-03-0312.5
Nice, we transposed our table to a table that we can use now. After that it is easy to use this SELECT as a subselect to perform some other filtering.
SELECT
id,
name,
date,
amount
FROM (SELECT
id,
max(CASE WHEN key ='name' THEN value END) AS name,
max(CASE WHEN key ='date' THEN value END) ASdate,
sum(CASE WHEN key ='amount' THEN value END) AS amount
FROM forms
GROUP BY id)
WHERE amount >20
id name date amount
-- ---- ---- -----2 Sam 2016-03-0232.0
4. Concatenate values with ||
If your table has columns that you need to concatenate into a single value, you can use ||.
SELECT (hello ||''|| world) AS mesasge
FROM (SELECT'Hello'as hello, 'World'as world);
> Hello World
5. Attach databases to JOIN on tables from different databases
If your query requires a JOIN between tables in different databases, you can use attach 'second-database.db' as second;. This will allow you to have access to the tables in second-database.db.
attach 'second-database.db'as second;
SELECT*FROM forms JOINsecond.othertableas other ONother.id=forms.id
6. Improve the performance of your query with EXPLAIN QUERY PLAN
If your queries are slow, it is probably because your table isn’t indexed correctly. In order to pinpoint the issue, you can use EXPLAIN QUERY PLAN (your query). The result of this command will give you guidance on what to index in your table.
Using the same table as 3. we can run EXPLAIN QUERY PLAN on some queries and see the result.
EXPLAIN QUERY PLAN SELECT*FROM forms WHERE id =2;
0|0|0|SCAN TABLE forms
SCAN TABLE is the worst result you can get. Since we querying on id, let’s create an index on id.
CREATEINDEXIF NOT EXISTS idx_forms_id ON forms (id);
EXPLAIN QUERY PLAN SELECT*FROM forms WHERE id =2;
0|0|0|SEARCH TABLE forms USING INDEX idx_forms_id (id=?)
Conclusion
SQLite has a lot of cool features and there are many more features that I haven’t discovered yet. Thanks to @nbevans for showing me how to use some of these features. Learning how to use these features really helped me to write better queries and ultimately helped in improving the performance of our system. Hope you learnt something new today with this post and if you have any question, leave it here or hit me on Twitter @Kimserey_Lam. See you next time!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters