Basic
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';")
head(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
Removing duplicates, and ordering results
df = dbGetQuery(con, "
SELECT distinct(surname)
FROM members
ORDER BY surname ASC LIMIT 10;")
head(df)
## surname
## 1 Bader
## 2 Baker
## 3 Boothe
## 4 Butters
## 5 Coplin
## 6 Crumpet
Combining results from multiple queries
df = dbGetQuery(con, "
SELECT surname
FROM members UNION
SELECT name from facilities;")
head(df)
## surname
## 1 Table Tennis
## 2 Tupperware
## 3 Massage Room 2
## 4 Jones
## 5 Boothe
## 6 Massage Room 1
Simple aggregation
df = dbGetQuery(con, "
SELECT MAX(joindate) AS latest
FROM members;
")
head(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);")
head(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 b.starttime as Tiempo_inicio
FROM bookings b inner join members m
on m.memid = b.memid
where
m.firstname='David'
and m.surname='Farrell';
")
head(df)
## tiempo_inicio
## 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
Work out the start times of bookings for tennis courts
df = dbGetQuery(con, "
select b.starttime as tiempo_inicio, f.name as nmbre
from
facilities f
inner join bookings b
on f.facid = b.facid
where
f.name in ('Tennis Court 2','Tennis Court 1') and
b.starttime >= '2012-09-21' and
b.starttime < '2012-09-22'
order by b.starttime;
")
head(df)
## tiempo_inicio nmbre
## 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
Produce a list of all members who have recommended another
member
df = dbGetQuery(con, "
select mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
from
members mems
left outer join members recs
on recs.memid = mems.recommendedby
order by memsname, memfname;
")
head(df)
## memfname memsname recfname recsname
## 1 Florence Bader Ponder Stibbons
## 2 Anne Baker Ponder Stibbons
## 3 Timothy Baker Jemima Farrell
## 4 Tim Boothe Tim Rownam
## 5 Gerald Butters Darren Smith
## 6 Joan Coplin Timothy Baker
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
head(df)
## data frame with 0 columns and 0 rows
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
head(df)
## data frame with 0 columns and 0 rows
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
head(df)
## data frame with 0 columns and 0 rows
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
head(df)
## data frame with 0 columns and 0 rows
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
head(df)
## data frame with 0 columns and 0 rows
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
head(df)
## data frame with 0 columns and 0 rows
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
head(df)
## data frame with 0 columns and 0 rows
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;
")
head(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
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