======================DATAFRAMES======================
data = [
(0, "06-26-2011", 300.4, "Exercise", "GymnasticsPro", "cash"),
(1, "05-26-2011", 200.0, "Exercise Band", "Weightlifting", "credit"),
(2, "06-01-2011", 300.4, "Exercise", "Gymnastics Pro", "cash"),
(3, "06-05-2011", 100.0, "Gymnastics", "Rings", "credit"),
(4, "12-17-2011", 300.0, "Team Sports", "Field", "cash"),
(5, "02-14-2011", 200.0, "Gymnastics", None, "cash"),
(6, "06-05-2011", 100.0, "Exercise", "Rings", "credit"),
(7, "12-17-2011", 300.0, "Team Sports", "Field", "cash"),
(8, "02-14-2011", 200.0, "Gymnastics", None, "cash")
]
df = [Link](data, ["id", "tdate", "amount", "category", "product", "spendby"])
[Link]()
data2 = [
(4, "12-17-2011", 300.0, "Team Sports", "Field", "cash"),
(5, "02-14-2011", 200.0, "Gymnastics", None, "cash"),
(6, "02-14-2011", 200.0, "Winter", None, "cash"),
(7, "02-14-2011", 200.0, "Winter", None, "cash")
]
df1 = [Link](data2, ["id", "tdate", "amount", "category", "product", "spendby"])
[Link]()
data4 = [
(1, "raj"),
(2, "ravi"),
(3, "sai"),
(5, "rani")
]
cust = [Link](data4, ["id", "name"])
[Link]()
data3 = [
(1, "mouse"),
(3, "mobile"),
(7, "laptop")
]
prod = [Link](data3, ["id", "product"])
[Link]()
[Link]("df")
[Link]("df1")
[Link]("cust")
[Link]("prod")
========================FILTERS========================
[Link]("select id,tdate from df").show()
[Link]("select * from df where category = 'Exercise'").show()
[Link]("select id,tdate,category,spendby from df where category = 'Exercise' and spendby =
'cash'").show()
[Link]("select * from df where category in ('Exercise','Gymnastics')").show()
[Link]("select * from df where product like '%Gymnastics%'").show()
[Link]("select * from df where category != 'Exercise'").show()
[Link]("select * from df where category not in ('Exercise','Gymnastics')").show()
[Link]("select * from df where product is null").show()
[Link]("select * from df where product is not null").show()
[Link]("select max(id) as idmax from df").show()
[Link]("select min(id) as idmin from df").show()
[Link]("select count(1) from df").show()
[Link]("select *, case when spendby='cash' then 1 else 0 end as status from df").show()
[Link]("select id,category,concat(id,'-',category) as condata from df").show()
[Link]("select id,category,product,concat_ws('-',id,category,product) as condata from
df").show()
[Link]("select category,lower(category) as lower from df")
[Link]("select category,upper(category) as Upper from df").show()
[Link]("select amount,ceil(amount) as Ceil from df").show()
[Link]("select amount,round(amount) as Round from df").show()
[Link]("select product,coalesce(product,'NA') as nullrep from df").show()
[Link]("select distinct category from df").show()
[Link]("select distinct category,spendby from df").show()
[Link]("select substring(product,1,10) as sub from df").show()
[Link]("select product,split(product,' ')[0] as split from df").show()
[Link]("select * from df union all select * from df1").show()
[Link]("select * from df union select * from df1 order by id").show()
[Link]("select category,sum(amount) as sum from df group by category").show()
[Link]("select category,spendby,sum(amount) as sum from df group by
category,spendby").show()
[Link]("select category,spendby,sum(amount) as sum,count(amount) as cnt from df group by
category,spendby").show()
[Link]("select category,max(amount) as max from df group by category").show()
[Link]("select category,max(amount) as max from df group by category order by
category").show()
[Link]("select category,max(amount) as max from df group by category order by category
desc").show()
[Link]("select category,amount,row_number() over (partition by category order by amount
desc) as row_number from df").show()
[Link]("select category,amount,dense_rank() over (partition by category order by amount
desc) as row_number from df").show()
[Link]("select category,amount,rank() over (partition by category order by amount desc) as
row_number from df").show()
[Link]("select category,amount,lead(amount) over (partition by category order by amount
desc) as lead from df").show()
[Link]("select category,amount,lag(amount) over (partition by category order by amount desc)
as lag from df").show()
[Link]("select category,count(category) as cnt from df group by category having
count(category)>1").show()
[Link]("select a.*,[Link] from cust a join prod b on [Link]=[Link]").show()
[Link]("select a.*,[Link] from cust a left join prod b on [Link]=[Link]").show()
[Link]("select a.*,[Link] from cust a right join prod b on [Link]=[Link]").show()
[Link]("select a.*,[Link] from cust a full join prod b on [Link]=[Link]").show()
[Link]("select a.* from cust a left anti join prod b on [Link]=[Link]").show()