R Markdown
library(DBI)
## Warning: package 'DBI' was built under R version 4.3.3
library(RPostgreSQL)
## Warning: package 'RPostgreSQL' was built under R version 4.3.3
con <- dbConnect(RPostgres::Postgres(),
dbname = "myname_db",
host = "localhost",
port = 5432,
user = "myname_user",
password = "password")
dbListTables(conn = con)
## [1] "facilities" "bookings" "members"
# Construye la consulta para borrar la tabla
query1 <- paste("DROP TABLE IF EXISTS facilities")
query2 <- paste("DROP TABLE IF EXISTS bookings")
query3 <- paste("DROP TABLE IF EXISTS members")
# Ejecuta la consulta para borrar la tabla
dbExecute(con, query1)
## [1] 0
dbExecute(con, query2)
## [1] 0
dbExecute(con, query3)
## [1] 0
# Crea la tabla facilities
query1 <- "
CREATE TABLE facilities (
facid integer NOT NULL,
name character varying(100) NOT NULL,
membercost numeric NOT NULL,
guestcost numeric NOT NULL,
initialoutlay numeric NOT NULL,
monthlymaintenance numeric NOT NULL
);"
query2 <- "
CREATE TABLE bookings (
bookid integer NOT NULL,
facid integer NOT NULL,
memid integer NOT NULL,
starttime timestamp without time zone NOT NULL,
slots integer NOT NULL
);"
query3 <- "
CREATE TABLE members (
memid integer NOT NULL,
surname character varying(200) NOT NULL,
firstname character varying(200) NOT NULL,
address character varying(300) NOT NULL,
zipcode integer NOT NULL,
telephone character varying(20) NOT NULL,
recommendedby integer,
joindate timestamp without time zone NOT NULL
);"
# Ejecuta la consulta
dbExecute(con, query1)
## [1] 0
dbExecute(con, query2)
## [1] 0
dbExecute(con, query3)
## [1] 0
Retrieve everything from a table
df = dbGetQuery(con, "SELECT * FROM facilities;")
head(df)
## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 0 Tennis Court 1 5 25.0 10000 200
## 2 1 Tennis Court 2 5 25.0 8000 200
## 3 2 Badminton Court 0 15.5 4000 50
## 4 3 Table Tennis 0 5.0 320 10
## 5 4 Massage Room 1 35 80.0 4000 3000
## 6 5 Massage Room 2 35 80.0 4000 3000
Retrieve specific columns from a table
df = dbGetQuery(con, "SELECT name,membercost FROM facilities;")
head(df)
## name membercost
## 1 Tennis Court 1 5
## 2 Tennis Court 2 5
## 3 Badminton Court 0
## 4 Table Tennis 0
## 5 Massage Room 1 35
## 6 Massage Room 2 35
Control which rows are retrieved
df = dbGetQuery(con, "SELECT * FROM facilities WHERE membercost <> 0")
head(df)
## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 0 Tennis Court 1 5.0 25.0 10000 200
## 2 1 Tennis Court 2 5.0 25.0 8000 200
## 3 4 Massage Room 1 35.0 80.0 4000 3000
## 4 5 Massage Room 2 35.0 80.0 4000 3000
## 5 6 Squash Court 3.5 17.5 5000 80
Control which rows are retrieved - part 2
df = dbGetQuery(con, "SELECT facid,name,membercost,monthlymaintenance FROM facilities WHERE membercost <> 0 AND membercost < monthlymaintenance/50;")
head(df)
## facid name membercost monthlymaintenance
## 1 4 Massage Room 1 35 3000
## 2 5 Massage Room 2 35 3000
Basic string searches
df = dbGetQuery(con, "SELECT * FROM facilities WHERE name LIKE '%Tennis%';")
head(df)
## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 0 Tennis Court 1 5 25 10000 200
## 2 1 Tennis Court 2 5 25 8000 200
## 3 3 Table Tennis 0 5 320 10
Matching against multiple possible values
df = dbGetQuery(con, "SELECT * FROM facilities WHERE facid IN (1,5);")
head(df)
## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 1 Tennis Court 2 5 25 8000 200
## 2 5 Massage Room 2 35 80 4000 3000
Classify results into buckets
df = dbGetQuery(con, "SELECT name, CASE WHEN (monthlymaintenance>100) THEN 'expensive' ELSE 'cheap' END AS cost
FROM facilities;")
head(df)
## name cost
## 1 Tennis Court 1 expensive
## 2 Tennis Court 2 expensive
## 3 Badminton Court cheap
## 4 Table Tennis cheap
## 5 Massage Room 1 expensive
## 6 Massage Room 2 expensive
Working with dates
df = dbGetQuery(con, "SELECT memid,surname,firstname,joindate FROM members WHERE joindate >= '2012-09-01';")
df
## memid surname firstname joindate
## 1 24 Sarwin Ramnaresh 2012-09-01 08:44:42
## 2 26 Jones Douglas 2012-09-02 18:43:05
## 3 27 Rumney Henrietta 2012-09-05 08:42:35
## 4 28 Farrell David 2012-09-15 08:22:05
## 5 29 Worthington-Smyth Henry 2012-09-17 12:27:15
## 6 30 Purview Millicent 2012-09-18 19:04:01
## 7 33 Tupperware Hyacinth 2012-09-18 19:32:05
## 8 35 Hunt John 2012-09-19 11:32:45
## 9 36 Crumpet Erica 2012-09-22 08:36:38
## 10 37 Smith Darren 2012-09-26 18:08:45
Removing duplicates, and ordering results
df = dbGetQuery(con, "SELECT distinct(surname) FROM members ORDER BY surname ASC LIMIT 10;")
df
## surname
## 1 Bader
## 2 Baker
## 3 Boothe
## 4 Butters
## 5 Coplin
## 6 Crumpet
## 7 Dare
## 8 Farrell
## 9 Genting
## 10 GUEST
Combining results from multiple queries
df = dbGetQuery(con, "SELECT surname FROM members UNION SELECT name from facilities;")
df
## surname
## 1 Table Tennis
## 2 Tupperware
## 3 Massage Room 2
## 4 Jones
## 5 Boothe
## 6 Massage Room 1
## 7 Smith
## 8 Owen
## 9 Purview
## 10 Snooker Table
## 11 Joplette
## 12 Hunt
## 13 GUEST
## 14 Badminton Court
## 15 Butters
## 16 Coplin
## 17 Genting
## 18 Bader
## 19 Worthington-Smyth
## 20 Pinker
## 21 Farrell
## 22 Dare
## 23 Squash Court
## 24 Tennis Court 1
## 25 Sarwin
## 26 Baker
## 27 Tennis Court 2
## 28 Rownam
## 29 Pool Table
## 30 Stibbons
## 31 Rumney
## 32 Tracy
## 33 Crumpet
## 34 Mackenzie
Simple aggregation
df = dbGetQuery(con, "SELECT MAX(joindate) AS latest FROM members;")
df
## latest
## 1 2012-09-26 18:08:45
More aggregation
df = dbGetQuery(con, "SELECT firstname,surname,joindate FROM members WHERE joindate IN (SELECT MAX(joindate) FROM members);")
df
## firstname surname joindate
## 1 Darren Smith 2012-09-26 18:08:45
Joins and Subqueries
Retrieve the start times of members’ bookings
df = dbGetQuery(con, "SELECT bks.starttime
FROM bookings bks
INNER JOIN members mems
ON mems.memid = bks.memid
WHERE mems.firstname = 'David' AND mems.surname = 'Farrell';")
df
## starttime
## 1 2012-09-18 09:00:00
## 2 2012-09-18 17:30:00
## 3 2012-09-18 13:30:00
## 4 2012-09-18 20:00:00
## 5 2012-09-19 09:30:00
## 6 2012-09-19 15:00:00
## 7 2012-09-19 12:00:00
## 8 2012-09-20 15:30:00
## 9 2012-09-20 11:30:00
## 10 2012-09-20 14:00:00
## 11 2012-09-21 10:30:00
## 12 2012-09-21 14:00:00
## 13 2012-09-22 08:30:00
## 14 2012-09-22 17:00:00
## 15 2012-09-23 08:30:00
## 16 2012-09-23 17:30:00
## 17 2012-09-23 19:00:00
## 18 2012-09-24 08:00:00
## 19 2012-09-24 16:30:00
## 20 2012-09-24 12:30:00
## 21 2012-09-25 15:30:00
## 22 2012-09-25 17:00:00
## 23 2012-09-26 13:00:00
## 24 2012-09-26 17:00:00
## 25 2012-09-27 08:00:00
## 26 2012-09-28 11:30:00
## 27 2012-09-28 09:30:00
## 28 2012-09-28 13:00:00
## 29 2012-09-29 16:00:00
## 30 2012-09-29 10:30:00
## 31 2012-09-29 13:30:00
## 32 2012-09-29 14:30:00
## 33 2012-09-29 17:30:00
## 34 2012-09-30 14:30:00
Work out the start times of bookings for tennis courts
df = dbGetQuery(con, "SELECT bks.starttime, fac.name
FROM bookings bks
INNER JOIN facilities fac
ON bks.facid = fac.facid
WHERE name LIKE 'Tennis%' AND starttime > '2012-09-21' AND starttime < '2012-09-22'
ORDER BY starttime ASC;")
df
## starttime name
## 1 2012-09-21 08:00:00 Tennis Court 1
## 2 2012-09-21 08:00:00 Tennis Court 2
## 3 2012-09-21 09:30:00 Tennis Court 1
## 4 2012-09-21 10:00:00 Tennis Court 2
## 5 2012-09-21 11:30:00 Tennis Court 2
## 6 2012-09-21 12:00:00 Tennis Court 1
## 7 2012-09-21 13:30:00 Tennis Court 1
## 8 2012-09-21 14:00:00 Tennis Court 2
## 9 2012-09-21 15:30:00 Tennis Court 1
## 10 2012-09-21 16:00:00 Tennis Court 2
## 11 2012-09-21 17:00:00 Tennis Court 1
## 12 2012-09-21 18:00:00 Tennis Court 2
Produce a list of all members who have recommended another
member
df = dbGetQuery(con, "SELECT DISTINCT mem.firstname, mem.surname
FROM members mem
INNER JOIN members memb
ON mem.memid = memb.recommendedby
ORDER BY surname, firstname
;")
df
## firstname surname
## 1 Florence Bader
## 2 Timothy Baker
## 3 Gerald Butters
## 4 Jemima Farrell
## 5 Matthew Genting
## 6 David Jones
## 7 Janice Joplette
## 8 Millicent Purview
## 9 Tim Rownam
## 10 Darren Smith
## 11 Tracy Smith
## 12 Ponder Stibbons
## 13 Burton Tracy
Produce a list of all members who have used a tennis court
df = dbGetQuery(con, "
select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility
from
members mems
inner join bookings bks
on mems.memid = bks.memid
inner join facilities facs
on bks.facid = facs.facid
where
facs.name in ('Tennis Court 2','Tennis Court 1')
order by member, facility
")
head(df)
## member facility
## 1 Anne Baker Tennis Court 1
## 2 Anne Baker Tennis Court 2
## 3 Burton Tracy Tennis Court 1
## 4 Burton Tracy Tennis Court 2
## 5 Charles Owen Tennis Court 1
## 6 Charles Owen Tennis Court 2
Produce a list of costly bookings
df = dbGetQuery(con, "
select mems.firstname || ' ' || mems.surname as member,
facs.name as facility,
case
when mems.memid = 0 then
bks.slots*facs.guestcost
else
bks.slots*facs.membercost
end as cost
from
members mems
inner join bookings bks
on mems.memid = bks.memid
inner join facilities facs
on bks.facid = facs.facid
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15' and (
(mems.memid = 0 and bks.slots*facs.guestcost > 30) or
(mems.memid != 0 and bks.slots*facs.membercost > 30)
)
order by cost desc;
")
head(df)
## member facility cost
## 1 GUEST GUEST Massage Room 2 320
## 2 GUEST GUEST Massage Room 1 160
## 3 GUEST GUEST Massage Room 1 160
## 4 GUEST GUEST Massage Room 1 160
## 5 GUEST GUEST Tennis Court 2 150
## 6 Jemima Farrell Massage Room 1 140
Produce a list of all members, along with their recommender, using
no joins.
df = dbGetQuery(con, "
select distinct mems.firstname || ' ' || mems.surname as member,
(select recs.firstname || ' ' || recs.surname as recommender
from members recs
where recs.memid = mems.recommendedby
)
from
members mems
order by member;
")
head(df)
## member recommender
## 1 Anna Mackenzie Darren Smith
## 2 Anne Baker Ponder Stibbons
## 3 Burton Tracy <NA>
## 4 Charles Owen Darren Smith
## 5 Darren Smith <NA>
## 6 David Farrell <NA>
Produce a list of costly bookings, using a subquery
df = dbGetQuery(con, "
select member, facility, cost from (
select
mems.firstname || ' ' || mems.surname as member,
facs.name as facility,
case
when mems.memid = 0 then
bks.slots*facs.guestcost
else
bks.slots*facs.membercost
end as cost
from
members mems
inner join bookings bks
on mems.memid = bks.memid
inner join facilities facs
on bks.facid = facs.facid
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15'
) as bookings
where cost > 30
order by cost desc;
")
head(df)
## member facility cost
## 1 GUEST GUEST Massage Room 2 320
## 2 GUEST GUEST Massage Room 1 160
## 3 GUEST GUEST Massage Room 1 160
## 4 GUEST GUEST Massage Room 1 160
## 5 GUEST GUEST Tennis Court 2 150
## 6 Jemima Farrell Massage Room 1 140
Modifying Data
Insert some data into a table
df = dbGetQuery(con, "
insert into facilities
(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
values (9, 'Spa', 20, 30, 100000, 800);
")
## Warning: Don't need to call dbFetch() for statements, only for queries
Insert multiple rows of data into a table
df = dbGetQuery(con, "
insert into facilities
(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
values
(9, 'Spa', 20, 30, 100000, 800),
(10, 'Squash Court 2', 3.5, 17.5, 5000, 80);
")
## Warning: Don't need to call dbFetch() for statements, only for queries
Update some existing data
df = dbGetQuery(con, "
update facilities
set initialoutlay = 10000
where facid = 1;
")
## Warning: Don't need to call dbFetch() for statements, only for queries
Update multiple rows and columns at the same time
df = dbGetQuery(con, "
update facilities
set
membercost = 6,
guestcost = 30
where facid in (0,1);
")
## Warning: Don't need to call dbFetch() for statements, only for queries
Update a row based on the contents of another row
df = dbGetQuery(con, "
update facilities facs
set
membercost = (select membercost * 1.1 from facilities where facid = 0),
guestcost = (select guestcost * 1.1 from facilities where facid = 0)
where facs.facid = 1;
")
## Warning: Don't need to call dbFetch() for statements, only for queries
Delete all bookings
df = dbGetQuery(con, "
--delete from bookings;
")
## Warning: Don't need to call dbFetch() for statements, only for queries
head(df)
## data frame with 0 columns and 0 rows
Delete a member from the cd.members table
df = dbGetQuery(con, "
--delete from members where memid = 37;
")
## Warning: Don't need to call dbFetch() for statements, only for queries
Delete based on a subquery
df = dbGetQuery(con, "
--delete from members where memid not in (select memid from bookings);
")
## Warning: Don't need to call dbFetch() for statements, only for queries
Aggregation
Count the number of facilities
df = dbGetQuery(con, "
select count(*) from facilities;
")
head(df)
## count
## 1 12
Count the number of expensive facilities
df = dbGetQuery(con, "
select count(*) from facilities where guestcost >= 10;
")
head(df)
## count
## 1 9
Count the number of recommendations each member makes.
df = dbGetQuery(con, "
select recommendedby, count(*)
from members
where recommendedby is not null
group by recommendedby
order by recommendedby;
")
head(df)
## recommendedby count
## 1 1 5
## 2 2 3
## 3 3 1
## 4 4 2
## 5 5 1
## 6 6 1
List the total slots booked per facility
df = dbGetQuery(con, "
select facid, sum(slots) as Total_Slots
from bookings
group by facid
order by facid;
")
head(df)
## facid total_slots
## 1 0 1320
## 2 1 1278
## 3 2 1209
## 4 3 830
## 5 4 1404
## 6 5 228
List the total slots booked per facility in a given month
df = dbGetQuery(con, "
select facid, sum(slots) as Total_Slots
from bookings
where
starttime >= '2012-09-01'
and starttime < '2012-10-01'
group by facid
order by sum(slots);
")
head(df)
## facid total_slots
## 1 5 122
## 2 3 422
## 3 7 426
## 4 8 471
## 5 6 540
## 6 2 570
List the total slots booked per facility per month
df = dbGetQuery(con, "
select facid, extract(month from starttime) as month, sum(slots) as Total_Slots
from bookings
where extract(year from starttime) = 2012
group by facid, month
order by facid, month;
")
head(df)
## facid month total_slots
## 1 0 7 270
## 2 0 8 459
## 3 0 9 591
## 4 1 7 207
## 5 1 8 483
## 6 1 9 588
Find the count of members who have made at least one booking
df = dbGetQuery(con, "
select count(distinct memid) from bookings
")
head(df)
## count
## 1 30
List facilities with more than 1000 slots booked
df = dbGetQuery(con, "
select facid, sum(slots) as Total_Slots
from bookings
group by facid
having sum(slots) > 1000
order by facid
")
head(df)
## facid total_slots
## 1 0 1320
## 2 1 1278
## 3 2 1209
## 4 4 1404
## 5 6 1104
Find the total revenue of each facility
df = dbGetQuery(con, "
select facs.name, sum(slots * case
when memid = 0 then facs.guestcost
else facs.membercost
end) as revenue
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
order by revenue;
")
head(df)
## name revenue
## 1 Table Tennis 180.0
## 2 Snooker Table 240.0
## 3 Pool Table 270.0
## 4 Badminton Court 1906.5
## 5 Squash Court 13468.0
## 6 Massage Room 2 15810.0
Find facilities with a total revenue less than 1000
df = dbGetQuery(con, "
select name, revenue from (
select facs.name, sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) as revenue
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as agg where revenue < 1000
order by revenue;
")
head(df)
## name revenue
## 1 Table Tennis 180
## 2 Snooker Table 240
## 3 Pool Table 270
Output the facility id that has the highest number of slots
booked
df = dbGetQuery(con, "
select facid, sum(slots) as Total_Slots
from bookings
group by facid
order by sum(slots) desc
LIMIT 1;
")
head(df)
## facid total_slots
## 1 4 1404
List the total slots booked per facility per month, part 2
df = dbGetQuery(con, "
select facid, extract(month from starttime) as month, sum(slots) as slots
from bookings
where
starttime >= '2012-01-01'
and starttime < '2013-01-01'
group by rollup(facid, month)
order by facid, month;
")
head(df)
## facid month slots
## 1 0 7 270
## 2 0 8 459
## 3 0 9 591
## 4 0 NA 1320
## 5 1 7 207
## 6 1 8 483
List the total hours booked per named facility
df = dbGetQuery(con, "
select facs.facid, facs.name,
trim(to_char(sum(bks.slots)/2.0, '9999999999999999D99')) as Total_Hours
from bookings bks
inner join facilities facs
on facs.facid = bks.facid
group by facs.facid, facs.name
order by facs.facid;
")
head(df)
## facid name total_hours
## 1 0 Tennis Court 1 660.00
## 2 1 Tennis Court 2 639.00
## 3 2 Badminton Court 604.50
## 4 3 Table Tennis 415.00
## 5 4 Massage Room 1 702.00
## 6 5 Massage Room 2 114.00
List each member’s first booking after September 1st 2012
df = dbGetQuery(con, "
select mems.surname, mems.firstname, mems.memid, min(bks.starttime) as starttime
from bookings bks
inner join members mems on
mems.memid = bks.memid
where starttime >= '2012-09-01'
group by mems.surname, mems.firstname, mems.memid
order by mems.memid;
")
head(df)
## surname firstname memid starttime
## 1 GUEST GUEST 0 2012-09-01 08:00:00
## 2 Smith Darren 1 2012-09-01 09:00:00
## 3 Smith Tracy 2 2012-09-01 11:30:00
## 4 Rownam Tim 3 2012-09-01 16:00:00
## 5 Joplette Janice 4 2012-09-01 15:00:00
## 6 Butters Gerald 5 2012-09-02 12:30:00
Produce a list of member names, with each row containing the total
member count
df = dbGetQuery(con, "
select count(*) over(), firstname, surname
from members
order by joindate
")
head(df)
## count firstname surname
## 1 31 GUEST GUEST
## 2 31 Darren Smith
## 3 31 Tracy Smith
## 4 31 Tim Rownam
## 5 31 Janice Joplette
## 6 31 Gerald Butters
Produce a numbered list of members
df = dbGetQuery(con, "
select row_number() over(order by joindate), firstname, surname
from members
order by joindate
")
head(df)
## row_number firstname surname
## 1 1 GUEST GUEST
## 2 2 Darren Smith
## 3 3 Tracy Smith
## 4 4 Tim Rownam
## 5 5 Janice Joplette
## 6 6 Gerald Butters
Output the facility id that has the highest number of slots booked,
again
df = dbGetQuery(con, "
select facid, total from (
select facid, sum(slots) total, rank() over (order by sum(slots) desc) rank
from bookings
group by facid
) as ranked
where rank = 1
")
head(df)
## facid total
## 1 4 1404
Rank members by (rounded) hours used
df = dbGetQuery(con, "
select firstname, surname,
((sum(bks.slots)+10)/20)*10 as hours,
rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
from bookings bks
inner join members mems
on bks.memid = mems.memid
group by mems.firstname,surname,mems.memid
order by rank, surname, firstname;
")
df
## firstname surname hours rank
## 1 GUEST GUEST 1200 1
## 2 Darren Smith 340 2
## 3 Tim Rownam 330 3
## 4 Tim Boothe 220 4
## 5 Tracy Smith 220 4
## 6 Gerald Butters 210 6
## 7 Burton Tracy 180 7
## 8 Charles Owen 170 8
## 9 Janice Joplette 160 9
## 10 Anne Baker 150 10
## 11 Timothy Baker 150 10
## 12 David Jones 150 10
## 13 Nancy Dare 130 13
## 14 Florence Bader 120 14
## 15 Anna Mackenzie 120 14
## 16 Ponder Stibbons 120 14
## 17 Jack Smith 110 17
## 18 Jemima Farrell 90 18
## 19 David Pinker 80 19
## 20 Ramnaresh Sarwin 80 19
## 21 Matthew Genting 70 21
## 22 Joan Coplin 50 22
## 23 David Farrell 30 23
## 24 Henry Worthington-Smyth 30 23
## 25 John Hunt 20 25
## 26 Douglas Jones 20 25
## 27 Millicent Purview 20 25
## 28 Henrietta Rumney 20 25
## 29 Erica Crumpet 10 29
## 30 Hyacinth Tupperware 10 29
Find the top three revenue generating facilities
df = dbGetQuery(con, "
select name, rank from (
select facs.name as name, rank() over (order by sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) desc) as rank
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as subq
where rank <= 3
order by rank;
")
head(df)
## name rank
## 1 Massage Room 1 1
## 2 Tennis Court 2 2
## 3 Tennis Court 1 3
Classify facilities by value
df = dbGetQuery(con, "
select name, case when class=1 then 'high'
when class=2 then 'average'
else 'low'
end revenue
from (
select facs.name as name, ntile(3) over (order by sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) desc) as class
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as subq
order by class, name;
")
head(df)
## name revenue
## 1 Massage Room 1 high
## 2 Tennis Court 1 high
## 3 Tennis Court 2 high
## 4 Badminton Court average
## 5 Massage Room 2 average
## 6 Squash Court average
Calculate the payback time for each facility
df = dbGetQuery(con, "
select facs.name as name,
facs.initialoutlay/((sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end)/3) - facs.monthlymaintenance) as months
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name,facs.initialoutlay,facs.guestcost,facs.monthlymaintenance
order by name;
")
head(df)
## name months
## 1 Badminton Court 6.8317677
## 2 Massage Room 1 0.1888574
## 3 Massage Room 2 1.7621145
## 4 Pool Table 5.3333333
## 5 Snooker Table 6.9230769
## 6 Squash Court 1.1339583
Calculate a rolling average of total revenue
df = dbGetQuery(con, "
select dategen.date,
(
select sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) as rev
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
where bks.starttime > dategen.date - interval '14 days'
and bks.starttime < dategen.date + interval '1 day'
)/15 as revenue
from
(
select cast(generate_series(timestamp '2012-08-01',
'2012-08-31','1 day') as date) as date
) as dategen
order by dategen.date;
")
head(df)
## date revenue
## 1 2012-08-01 1191.793
## 2 2012-08-02 1219.320
## 3 2012-08-03 1229.460
## 4 2012-08-04 1245.207
## 5 2012-08-05 1228.933
## 6 2012-08-06 1254.360
Timestamps
Produce a timestamp for 1 a.m. on the 31st of August 2012
df = dbGetQuery(con, "
select timestamp '2012-08-31 01:00:00';
")
head(df)
## timestamp
## 1 2012-08-31 01:00:00
Subtract timestamps from each other
df = dbGetQuery(con, "
select timestamp '2012-08-31 01:00:00' - timestamp '2012-07-30 01:00:00' as interval;
")
head(df)
## interval
## 1 32 days
Generate a list of all the dates in October 2012
df = dbGetQuery(con, "
select generate_series(timestamp '2012-10-01', timestamp '2012-10-31', interval '1 day') as ts;
")
head(df)
## ts
## 1 2012-10-01
## 2 2012-10-02
## 3 2012-10-03
## 4 2012-10-04
## 5 2012-10-05
## 6 2012-10-06
Get the day of the month from a timestamp
df = dbGetQuery(con, "
select extract(day from timestamp '2012-08-31');
")
head(df)
## extract
## 1 31
Get the day of the month from a timestamp
df = dbGetQuery(con, "
select extract(epoch from (timestamp '2012-09-02 00:00:00' - '2012-08-31 01:00:00'));
")
head(df)
## extract
## 1 169200
Work out the number of days in each month of 2012
df = dbGetQuery(con, "select extract(month from cal.month) as month,
(cal.month + interval '1 month') - cal.month as length
from
(
select generate_series(timestamp '2012-01-01', timestamp '2012-12-01', interval '1 month') as month
) cal
order by month
;")
df
## month length
## 1 1 31 days
## 2 2 29 days
## 3 3 31 days
## 4 4 30 days
## 5 5 31 days
## 6 6 30 days
## 7 7 31 days
## 8 8 31 days
## 9 9 30 days
## 10 10 31 days
## 11 11 30 days
## 12 12 31 days
Work out the number of days remaining in the month
df = dbGetQuery(con, "select (date_trunc('month',ts.testts) + interval '1 month')
- date_trunc('day', ts.testts) as remaining
from (select timestamp '2012-02-11 01:00:00' as testts) ts
;")
df
## remaining
## 1 19 days
Work out the end time of bookings
df = dbGetQuery(con, "select starttime, starttime + slots*(interval '30 minutes') endtime
from bookings
order by endtime desc, starttime desc
limit 10
;")
df
## starttime endtime
## 1 2013-01-01 15:30:00 2013-01-01 16:00:00
## 2 2012-09-30 19:30:00 2012-09-30 20:30:00
## 3 2012-09-30 19:00:00 2012-09-30 20:30:00
## 4 2012-09-30 19:30:00 2012-09-30 20:00:00
## 5 2012-09-30 19:00:00 2012-09-30 20:00:00
## 6 2012-09-30 19:00:00 2012-09-30 20:00:00
## 7 2012-09-30 18:30:00 2012-09-30 20:00:00
## 8 2012-09-30 18:30:00 2012-09-30 20:00:00
## 9 2012-09-30 19:00:00 2012-09-30 19:30:00
## 10 2012-09-30 18:30:00 2012-09-30 19:30:00
Return a count of bookings for each month
df = dbGetQuery(con, "select date_trunc('month', starttime) as month, count(*)
from bookings
group by month
order by month
;")
df
## month count
## 1 2012-07-01 658
## 2 2012-08-01 1472
## 3 2012-09-01 1913
## 4 2013-01-01 1
Work out the utilisation percentage for each facility by month
df = dbGetQuery(con, "select name, month,
round((100*slots)/
cast(
25*(cast((month + interval '1 month') as date)
- cast (month as date)) as numeric),1) as utilisation
from (
select facs.facid, facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.facid, facs.name, month
) as inn
order by name, month
;")
df
## name month utilisation
## 1 Badminton Court 2012-07-01 23.2
## 2 Badminton Court 2012-08-01 59.2
## 3 Badminton Court 2012-09-01 76.0
## 4 Massage Room 1 2012-07-01 34.1
## 5 Massage Room 1 2012-08-01 63.5
## 6 Massage Room 1 2012-09-01 86.4
## 7 Massage Room 2 2012-07-01 3.1
## 8 Massage Room 2 2012-08-01 10.6
## 9 Massage Room 2 2012-09-01 16.3
## 10 Pool Table 2012-07-01 15.1
## 11 Pool Table 2012-08-01 41.5
## 12 Pool Table 2012-09-01 62.8
## 13 Pool Table 2013-01-01 0.1
## 14 Snooker Table 2012-07-01 20.1
## 15 Snooker Table 2012-08-01 42.1
## 16 Snooker Table 2012-09-01 56.8
## 17 Squash Court 2012-07-01 21.2
## 18 Squash Court 2012-08-01 51.6
## 19 Squash Court 2012-09-01 72.0
## 20 Table Tennis 2012-07-01 13.4
## 21 Table Tennis 2012-08-01 39.2
## 22 Table Tennis 2012-09-01 56.3
## 23 Tennis Court 1 2012-07-01 34.8
## 24 Tennis Court 1 2012-08-01 59.2
## 25 Tennis Court 1 2012-09-01 78.8
## 26 Tennis Court 2 2012-07-01 26.7
## 27 Tennis Court 2 2012-08-01 62.3
## 28 Tennis Court 2 2012-09-01 78.4
String Operations
Find facilities by a name prefix
df = dbGetQuery(con, "SELECT * FROM facilities WHERE name LIKE 'Tennis%'
;")
df
## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 0 Tennis Court 1 6.0 30 10000 200
## 2 1 Tennis Court 2 6.6 33 10000 200
Find telephone numbers with parentheses
df = dbGetQuery(con, "SELECT memid, telephone FROM members WHERE telephone ~ '[()]'
;")
df
## memid telephone
## 1 0 (000) 000-0000
## 2 3 (844) 693-0723
## 3 4 (833) 942-4710
## 4 5 (844) 078-4130
## 5 6 (822) 354-9973
## 6 7 (833) 776-4001
## 7 8 (811) 433-2547
## 8 9 (833) 160-3900
## 9 10 (855) 542-5251
## 10 11 (844) 536-8036
## 11 13 (855) 016-0163
## 12 14 (822) 163-3254
## 13 15 (833) 499-3527
## 14 20 (811) 972-1377
## 15 21 (822) 661-2898
## 16 22 (822) 499-2232
## 17 24 (822) 413-1470
## 18 27 (822) 989-8876
## 19 28 (855) 755-9876
## 20 29 (855) 894-3758
## 21 30 (855) 941-9786
## 22 33 (822) 665-5327
## 23 35 (899) 720-6978
## 24 36 (811) 732-4816
## 25 37 (822) 577-3541
Pad zip codes with leading zeroes
df = dbGetQuery(con, "SELECT lpad(cast(zipcode as char(5)),5,'0') zip FROM members ORDER BY zip;")
df
## zip
## 1 00000
## 2 00234
## 3 00234
## 4 04321
## 5 04321
## 6 10383
## 7 11986
## 8 23423
## 9 28563
## 10 33862
## 11 34232
## 12 43532
## 13 43533
## 14 45678
## 15 52365
## 16 54333
## 17 56754
## 18 57392
## 19 58393
## 20 64577
## 21 65332
## 22 65464
## 23 66796
## 24 68666
## 25 69302
## 26 75655
## 27 78533
## 28 80743
## 29 84923
## 30 87630
## 31 97676
Count the number of members whose surname starts with each letter of
the alphabet
df = dbGetQuery(con, "SELECT substr (mems.surname,1,1) as letter, count(*) as count
FROM members mems
GROUP BY letter
ORDER BY letter
;")
df
## letter count
## 1 B 5
## 2 C 2
## 3 D 1
## 4 F 2
## 5 G 2
## 6 H 1
## 7 J 3
## 8 M 1
## 9 O 1
## 10 P 2
## 11 R 2
## 12 S 6
## 13 T 2
## 14 W 1
Clean up telephone numbers
df = dbGetQuery(con, "SELECT memid, translate(telephone, '-() ', '') as telephone
FROM members
ORDER BY memid
;")
df
## memid telephone
## 1 0 0000000000
## 2 1 5555555555
## 3 2 5555555555
## 4 3 8446930723
## 5 4 8339424710
## 6 5 8440784130
## 7 6 8223549973
## 8 7 8337764001
## 9 8 8114332547
## 10 9 8331603900
## 11 10 8555425251
## 12 11 8445368036
## 13 12 8440765141
## 14 13 8550160163
## 15 14 8221633254
## 16 15 8334993527
## 17 16 8339410824
## 18 17 8114096734
## 19 20 8119721377
## 20 21 8226612898
## 21 22 8224992232
## 22 24 8224131470
## 23 26 8445368036
## 24 27 8229898876
## 25 28 8557559876
## 26 29 8558943758
## 27 30 8559419786
## 28 33 8226655327
## 29 35 8997206978
## 30 36 8117324816
## 31 37 8225773541
Recursive Queries
Find the upward recommendation chain for member ID 27
df = dbGetQuery(con, "with recursive recommenders(recommender) as (
select recommendedby from members where memid = 27
union all
select mems.recommendedby
from recommenders recs
inner join members mems
on mems.memid = recs.recommender
)
SELECT recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join members mems
on recs.recommender = mems.memid
ORDER BY memid DESC
;")
df
## recommender firstname surname
## 1 20 Matthew Genting
## 2 5 Gerald Butters
## 3 1 Darren Smith
Find the downward recommendation chain for member ID 1
df = dbGetQuery(con, "with recursive recommendeds(memid) as (
select memid from members where recommendedby = 1
union all
select mems.memid
from recommendeds recs
inner join members mems
on mems.recommendedby = recs.memid
)
select recs.memid, mems.firstname, mems.surname
from recommendeds recs
inner join members mems
on recs.memid = mems.memid
order by memid
;")
df
## memid firstname surname
## 1 4 Janice Joplette
## 2 5 Gerald Butters
## 3 7 Nancy Dare
## 4 10 Charles Owen
## 5 11 David Jones
## 6 14 Jack Smith
## 7 20 Matthew Genting
## 8 21 Anna Mackenzie
## 9 26 Douglas Jones
## 10 27 Henrietta Rumney
Produce a CTE that can return the upward recommendation chain for
any member
df = dbGetQuery(con, "with recursive recommenders(recommender, member) as (
select recommendedby, memid
from members
union all
select mems.recommendedby, recs.member
from recommenders recs
inner join members mems
on mems.memid = recs.recommender
)
select recs.member member, recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join members mems
on recs.recommender = mems.memid
where recs.member = 22 or recs.member = 12
order by recs.member asc, recs.recommender desc
;")
df
## member recommender firstname surname
## 1 12 9 Ponder Stibbons
## 2 12 6 Burton Tracy
## 3 22 16 Timothy Baker
## 4 22 13 Jemima Farrell