NewolympicData

 

Alison Bartosik 21 United States 2004 08-29-04 Synchronized Swimming 0 0 2 2
Anastasiya Davydova 21 Russia 2004 08-29-04 Synchronized Swimming 2 0 0 2
Anna Kozlova 31 United States 2004 08-29-04 Synchronized Swimming 0 0 2 2
Miya Tachibana 29 Japan 2004 08-29-04 Synchronized Swimming 0 2 0 2
Miho Takeda 27 Japan 2004 08-29-04 Synchronized Swimming 0 2 0 2
Anastasiya Yermakova 21 Russia 2004 08-29-04 Synchronized Swimming 2 0 0 2
Olga Brusnikina 21 Russia 2000 10-01-00 Synchronized Swimming 2 0 0 2
Mariya Kiselyova 25 Russia 2000 10-01-00 Synchronized Swimming 2 0 0 2
Miya Tachibana 25 Japan 2000 10-01-00 Synchronized Swimming 0 2 0 2
Miho Takeda 24 Japan 2000 10-01-00 Synchronized Swimming 0 2 0 2
Becky Adlington 23 Great Britain 2012 08-12-12 Swimming 0 0 2 2
Bronte Barratt 23 Australia 2012 08-12-12 Swimming 0 1 1 2
Elizabeth Beisel 19 United States 2012 08-12-12 Swimming 0 1 1 2
Mireia Belmonte 21 Spain 2012 08-12-12 Swimming 0 2 0 2
Ricky Berens 24 United States 2012 08-12-12 Swimming 1 1 0 2
Aleksandra Gerasimenya 26 Belarus 2012 08-12-12 Swimming 0 2 0 2
Brendan Hansen 30 United States 2012 08-12-12 Swimming 1 0 1 2
Jessica Hardy 25 United States 2012 08-12-12 Swimming 1 0 1 2
Chad le Clos 20 South Africa 2012 08-12-12 Swimming 1 1 0 2
Clément Lefert 24 France 2012 08-12-12 Swimming 1 1 0 2
Amaury Leveaux 26 France 2012 08-12-12 Swimming 1 1 0 2
James Magnussen 21 Australia 2012 08-12-12 Swimming 0 1 1 2
Takeshi Matsuda 28 Japan 2012 08-12-12 Swimming 0 1 1 2
Oussama Mellouli 28 Tunisia 2012 08-12-12 Swimming 1 0 1 2
Park Tae-Hwan 22 South Korea 2012 08-12-12 Swimming 0 2 0 2
Christian Sprenger 26 Australia 2012 08-12-12 Swimming 0 1 1 2
Jeremy Stravius 24 France 2012 08-12-12 Swimming 1 1 0 2
Aya Terakawa 27 Japan 2012 08-12-12 Swimming 0 0 2 2
Nick Thoman 26 United States 2012 08-12-12 Swimming 1 1 0 2
Marleen Veldhuis 33 Netherlands 2012 08-12-12 Swimming 0 1 1 2
Ye Shiwen 16 China 2012 08-12-12 Swimming 2 0 0 2
Becky Adlington 19 Great Britain 2008 08-24-08 Swimming 2 0 0 2
Leith Brodie 22 Australia 2008 08-24-08 Swimming 0 0 2 2
Cate Campbell 16 Australia 2008 08-24-08 Swimming 0 0 2 2
César Cielo Filho 21 Brazil 2008 08-24-08 Swimming 1 0 1 2
Hugues Duboscq 26 France 2008 08-24-08 Swimming 0 0 2 2
Felicity Galvez 23 Australia 2008 08-24-08 Swimming 2 0 0 2
Grant Hackett 28 Australia 2008 08-24-08 Swimming 0 1 1 2
Kara Lynn Joyce 22 United States 2008 08-24-08 Swimming 0 2 0 2
Amaury Leveaux 22 France 2008 08-24-08 Swimming 0 2 0 2
Christine Magnuson 22 United States 2008 08-24-08 Swimming 0 2 0 2
Patrick Murphy 24 Australia 2008 08-24-08 Swimming 0 0 2 2
Park Tae-Hwan 18 South Korea 2008 08-24-08 Swimming 1 1 0 2
Shayne Reese 25 Australia 2008 08-24-08 Swimming 1 0 1 2
Brenton Rickard 24 Australia 2008 08-24-08 Swimming 0 2 0 2
Mel Schlanger 21 Australia 2008 08-24-08 Swimming 1 0 1 2
Julia Smit 20 United States 2008 08-24-08 Swimming 0 1 1 2
Britta Steffen 24 Germany 2008 08-24-08 Swimming 2 0 0 2
Hayden Stoeckel 24 Australia 2008 08-24-08 Swimming 0 1 1 2
Matt Targett 22 Australia 2008 08-24-08 Swimming 0 1 1 2
Peter Vanderkaay 24 United States 2008 08-24-08 Swimming 1 0 1 2
Arkady Vyachanin 24 Russia 2008 08-24-08 Swimming 0 0 2 2
Garrett Weber-Gale 23 United States 2008 08-24-08 Swimming 2 0 0 2
Lindsay Benko 27 United States 2004 08-29-04 Swimming 1 1 0 2
Gary Hall Jr. 29 United States 2004 08-29-04 Swimming 1 0 1 2
Brooke Hanson 26 Australia 2004 08-29-04 Swimming 1 1 0 2
Kara Lynn Joyce 18 United States 2004 08-29-04 Swimming 0 2 0 2
Klete Keller 22 United States 2004 08-29-04 Swimming 1 0 1 2
Yana Klochkova 22 Ukraine 2004 08-29-04 Swimming 2 0 0 2
Rachel Komisarz 27 United States 2004 08-29-04 Swimming 1 1 0 2
Libby Lenton-Trickett 19 Australia 2004 08-29-04 Swimming 1 0 1 2
Jason Lezak 28 United States 2004 08-29-04 Swimming 1 0 1 2
Ryan Lochte 20 United States 2004 08-29-04 Swimming 1 1 0 2
Alice Mills 18 Australia 2004 08-29-04 Swimming 2 0 0 2
Tomomi Morita 19 Japan 2004 08-29-04 Swimming 0 0 2 2
Markus Rogan 22 Austria 2004 08-29-04 Swimming 0 2 0 2
Jenny Thompson 31 United States 2004 08-29-04 Swimming 0 2 0 2
Franziska van Almsick 26 Germany 2004 08-29-04 Swimming 0 0 2 2
Neil Walker 28 United States 2004 08-29-04 Swimming 1 0 1 2
Amanda Weir 18 United States 2004 08-29-04 Swimming 0 2 0 2
Takashi Yamamoto 26 Japan 2004 08-29-04 Swimming 0 1 1 2
Brooke Bennett 20 United States 2000 10-01-00 Swimming 2 0 0 2
Beatrice Coada-Caslaru 25 Romania 2000 10-01-00 Swimming 0 1 1 2
Josh Davis 28 United States 2000 10-01-00 Swimming 0 2 0 2
Tom Dolan 25 United States 2000 10-01-00 Swimming 1 1 0 2
Anthony Ervin 19 United States 2000 10-01-00 Swimming 1 1 0 2
Domenico Fioravanti 23 Italy 2000 10-01-00 Swimming 2 0 0 2
Grant Hackett 20 Australia 2000 10-01-00 Swimming 2 0 0 2
Geoff Huegill 21 Australia 2000 10-01-00 Swimming 0 1 1 2
Leisel Jones 15 Australia 2000 10-01-00 Swimming 0 2 0 2
Klete Keller 18 United States 2000 10-01-00 Swimming 0 1 1 2
Jason Lezak 24 United States 2000 10-01-00 Swimming 1 1 0 2
Diana Mocanu 16 Romania 2000 10-01-00 Swimming 2 0 0 2
Martina Moravcová 24 Slovakia 2000 10-01-00 Swimming 0 2 0 2
Ed Moses 20 United States 2000 10-01-00 Swimming 1 1 0 2
Diana Munz 18 United States 2000 10-01-00 Swimming 1 1 0 2
Mai Nakamura 21 Japan 2000 10-01-00 Swimming 0 1 1 2
Todd Pearson 22 Australia 2000 10-01-00 Swimming 2 0 0 2
Adam Pine 24 Australia 2000 10-01-00 Swimming 1 1 0 2
Claudia Poll 27 Costa Rica 2000 10-01-00 Swimming 0 0 2 2
Megan Quann-Jendrick 16 United States 2000 10-01-00 Swimming 2 0 0 2
Giaan Rooney 17 Australia 2000 10-01-00 Swimming 0 2 0 2
Courtney Shealy 22 United States 2000 10-01-00 Swimming 2 0 0 2
Ashley Tappin 25 United States 2000 10-01-00 Swimming 2 0 0 2
Stev Theloke 22 Germany 2000 10-01-00 Swimming 0 0 2 2
Amy Van Dyken 27 United States 2000 10-01-00 Swimming 2 0 0 2
Neil Walker 24 United States 2000 10-01-00 Swimming 1 1 0 2
J. R. Celski 19 United States 2010 02-28-10 Short-Track Speed Skating 0 0 2 2
Charles Hamelin 25 Canada 2010 02-28-10 Short-Track Speed Skating 2 0 0 2
Lee Ho-Seok 23 South Korea 2010 02-28-10 Short-Track Speed Skating 0 2 0 2
Park Seung-Hui 17 South Korea 2010 02-28-10 Short-Track Speed Skating 0 0 2 2
Katherine Reutter 21 United States 2010 02-28-10 Short-Track Speed Skating 0 1 1 2
Seong Si-Baek 22 South Korea 2010 02-28-10 Short-Track Speed Skating 0 2 0 2
Marianne St-Gelais 19 Canada 2010 02-28-10 Short-Track Speed Skating 0 2 0 2
François-Louis Tremblay 29 Canada 2010 02-28-10 Short-Track Speed Skating 1 0 1 2
Zhou Yang 18 China 2010 02-28-10 Short-Track Speed Skating 2 0 0 2
Choi Eun-Gyeong 21 South Korea 2006 02-26-06 Short-Track Speed Skating 1 1 0 2
Anouk Leblanc-Boucher 21 Canada 2006 02-26-06 Short-Track Speed Skating 0 1 1 2
François-Louis Tremblay 25 Canada 2006 02-26-06 Short-Track Speed Skating 0 2 0 2
Choi Eun-Gyeong 17 South Korea 2002 02-24-02 Short-Track Speed Skating 1 1 0 2
Go Gi-Hyeon 15 South Korea 2002 02-24-02 Short-Track Speed Skating 1 1 0 2
Jonathan Guilmette 23 Canada 2002 02-24-02 Short-Track Speed Skating 1 1 0 2
Li Jiajun 26 China 2002 02-24-02 Short-Track Speed Skating 0 1 1 2
Apolo Anton Ohno 19 United States 2002 02-24-02 Short-Track Speed Skating 1 1 0 2
Evgeniya Radanova 24 Bulgaria 2002 02-24-02 Short-Track Speed Skating 0 1 1 2
Mathieu Turcotte 25 Canada 2002 02-24-02 Short-Track Speed Skating 1 0 1 2
Wang Chunlu 23 China 2002 02-24-02 Short-Track Speed Skating 0 1 1 2
Yang Yang (S) 24 China 2002 02-24-02 Short-Track Speed Skating 0 1 1 2
Shani Davis 27 United States 2010 02-28-10 Speed Skating 1 1 0 2
Kristina Groves 33 Canada 2010 02-28-10 Speed Skating 0 1 1 2
Chad Hedrick 32 United States 2010 02-28-10 Speed Skating 0 1 1 2
Sven Kramer 23 Netherlands 2010 02-28-10 Speed Skating 1 0 1 2
Lee Seung-Hun 21 South Korea 2010 02-28-10 Speed Skating 1 1 0 2
Mo Tae-Beom 21 South Korea 2010 02-28-10 Speed Skating 1 1 0 2
Ivan Skobrev 27 Russia 2010 02-28-10 Speed Skating 0 1 1 2
Mark Tuitert 29 Netherlands 2010 02-28-10 Speed Skating 1 0 1 2
Joey Cheek 26 United States 2006 02-26-06 Speed Skating 1 1 0 2
Shani Davis 23 United States 2006 02-26-06 Speed Skating 1 1 0 2
Anni Friesinger-Postma 29 Germany 2006 02-26-06 Speed Skating 1 0 1 2
Kristina Groves 29 Canada 2006 02-26-06 Speed Skating 0 2 0 2
Clara Hughes 33 Canada 2006 02-26-06 Speed Skating 1 1 0 2
Sven Kramer 19 Netherlands 2006 02-26-06 Speed Skating 0 1 1 2
Claudia Pechstein 33 Germany 2006 02-26-06 Speed Skating 1 1 0 2
Carl Verheijen 30 Netherlands 2006 02-26-06 Speed Skating 0 0 2 2
Erben Wennemars 30 Netherlands 2006 02-26-06 Speed Skating 0 0 2 2
Ireen Wüst 19 Netherlands 2006 02-26-06 Speed Skating 1 0 1 2
Derek Parra 31 United States 2002 02-24-02 Speed Skating 1 1 0 2
Claudia Pechstein 29 Germany 2002 02-24-02 Speed Skating 2 0 0 2
Jennifer Rodriguez 25 United States 2002 02-24-02 Speed Skating 0 0 2 2
Simon Ammann 28 Switzerland 2010 02-28-10 Ski Jumping 2 0 0 2
Adam Malysz 32 Poland 2010 02-28-10 Ski Jumping 0 2 0 2
Matti Hautamäki 24 Finland 2006 02-26-06 Ski Jumping 0 2 0 2
Andreas Kofler 21 Austria 2006 02-26-06 Ski Jumping 1 1 0 2
Roar Ljøkelsøy 29 Norway 2006 02-26-06 Ski Jumping 0 0 2 2
Thomas Morgenstern 19 Austria 2006 02-26-06 Ski Jumping 2 0 0 2
Simon Ammann 20 Switzerland 2002 02-24-02 Ski Jumping 2 0 0 2
Sven Hannawald 27 Germany 2002 02-24-02 Ski Jumping 1 1 0 2
Matti Hautamäki 20 Finland 2002 02-24-02 Ski Jumping 0 1 1 2
Adam Malysz 24 Poland 2002 02-24-02 Ski Jumping 0 1 1 2
Niccolò Campriani 24 Italy 2012 08-12-12 Shooting 1 1 0 2
Jin Jong-O 32 South Korea 2012 08-12-12 Shooting 2 0 0 2
Olena Kostevych 27 Ukraine 2012 08-12-12 Shooting 0 0 2 2
Jin Jong-O 28 South Korea 2008 08-24-08 Shooting 1 1 0 2
Katerina Kurková-Emmons 24 Czech Republic 2008 08-24-08 Shooting 1 1 0 2
Lyubov Galkina 31 Russia 2004 08-29-04 Shooting 1 1 0 2
Mariya Grozdeva 32 Bulgaria 2004 08-29-04 Shooting 1 0 1 2
Lee Bo-Na 23 South Korea 2004 08-29-04 Shooting 0 1 1 2
Mikhail Nestruyev 35 Russia 2004 08-29-04 Shooting 1 1 0 2
Igor Basinsky 37 Belarus 2000 10-01-00 Shooting 0 1 1 2
Tao Luna 26 China 2000 10-01-00 Shooting 1 1 0 2
Kim Crow 26 Australia 2012 08-12-12 Rowing 0 1 1 2
Georgeta Damian-Andrunache 32 Romania 2008 08-24-08 Rowing 1 0 1 2
Viorica Susanu 32 Romania 2008 08-24-08 Rowing 1 0 1 2
Georgeta Damian-Andrunache 28 Romania 2004 08-29-04 Rowing 2 0 0 2
Viorica Susanu 28 Romania 2004 08-29-04 Rowing 2 0 0 2
Georgeta Damian-Andrunache 24 Romania 2000 10-01-00 Rowing 2 0 0 2
Doina Ignat 31 Romania 2000 10-01-00 Rowing 2 0 0 2
Pieta van Dishoeck 28 Netherlands 2000 10-01-00 Rowing 0 2 0 2
Eeke van Nes 31 Netherlands 2000 10-01-00 Rowing 0 2 0 2
Bill Demong 29 United States 2010 02-28-10 Nordic Combined 1 1 0 2
Bernhard Gruber 27 Austria 2010 02-28-10 Nordic Combined 1 0 1 2
Magnus Moan 22 Norway 2006 02-26-06 Nordic Combined 0 1 1 2
Ronny Ackermann 24 Germany 2002 02-24-02 Nordic Combined 0 2 0 2
Jaakko Tallus 20 Finland 2002 02-24-02 Nordic Combined 1 1 0 2
Denis Ablyazin 19 Russia 2012 08-12-12 Gymnastics 0 1 1 2
Chen Yibing 27 China 2012 08-12-12 Gymnastics 1 1 0 2
Gabby Douglas 16 United States 2012 08-12-12 Gymnastics 2 0 0 2
Feng Zhe 24 China 2012 08-12-12 Gymnastics 2 0 0 2
Sandra Izbasa 22 Romania 2012 08-12-12 Gymnastics 1 0 1 2
Viktoriya Komova 17 Russia 2012 08-12-12 Gymnastics 0 2 0 2
McKayla Maroney 16 United States 2012 08-12-12 Gymnastics 1 1 0 2
Marcel Nguyen 24 Germany 2012 08-12-12 Gymnastics 0 2 0 2
Mariya Paseka 17 Russia 2012 08-12-12 Gymnastics 0 1 1 2
Catalina Ponor 24 Romania 2012 08-12-12 Gymnastics 0 1 1 2
Louis Smith 23 Great Britain 2012 08-12-12 Gymnastics 0 1 1 2
Max Whitlock 19 Great Britain 2012 08-12-12 Gymnastics 0 0 2 2
Chen Yibing 23 China 2008 08-24-08 Gymnastics 2 0 0 2
Anton Golotsutskov 23 Russia 2008 08-24-08 Gymnastics 0 0 2 2
He Kexin 16 China 2008 08-24-08 Gymnastics 2 0 0 2
Jonathan Horton 22 United States 2008 08-24-08 Gymnastics 0 1 1 2
Sandra Izbasa 18 Romania 2008 08-24-08 Gymnastics 1 0 1 2
Li Xiaopeng 27 China 2008 08-24-08 Gymnastics 2 0 0 2
Kohei Uchimura 19 Japan 2008 08-24-08 Gymnastics 0 2 0 2
Xiao Qin 23 China 2008 08-24-08 Gymnastics 2 0 0 2
Alexandra Eremia 17 Romania 2004 08-29-04 Gymnastics 1 0 1 2
Annia Hatch 26 United States 2004 08-29-04 Gymnastics 0 2 0 2
Terin Humphrey 18 United States 2004 08-29-04 Gymnastics 0 2 0 2
Takehiro Kashima 24 Japan 2004 08-29-04 Gymnastics 1 0 1 2
Svetlana Khorkina 25 Russia 2004 08-29-04 Gymnastics 0 1 1 2
Courtney Kupets 18 United States 2004 08-29-04 Gymnastics 0 1 1 2
Anna Pavlova 16 Russia 2004 08-29-04 Gymnastics 0 0 2 2
Monica Rosu 17 Romania 2004 08-29-04 Gymnastics 2 0 0 2
Dana Sofronie 16 Romania 2004 08-29-04 Gymnastics 1 1 0 2
Hiroyuki Tomita 23 Japan 2004 08-29-04 Gymnastics 1 1 0 2
Marius Urzica 28 Romania 2004 08-29-04 Gymnastics 0 1 1 2
Isao Yoneda 26 Japan 2004 08-29-04 Gymnastics 1 0 1 2
Yordan Yovchev 31 Bulgaria 2004 08-29-04 Gymnastics 0 1 1 2
Oleksandr Beresh 22 Ukraine 2000 10-01-00 Gymnastics 0 1 1 2
Aleksey Bondarenko 22 Russia 2000 10-01-00 Gymnastics 0 1 1 2
Lee Ju-Hyeong 27 South Korea 2000 10-01-00 Gymnastics 0 1 1 2
Li Xiaopeng 19 China 2000 10-01-00 Gymnastics 2 0 0 2
Liu Xuan 21 China 2000 10-01-00 Gymnastics 1 0 1 2
Maria Olaru 18 Romania 2000 10-01-00 Gymnastics 1 1 0 2
Yelena Produnova 20 Russia 2000 10-01-00 Gymnastics 0 1 1 2
Andreea Raducan 16 Romania 2000 10-01-00 Gymnastics 1 1 0 2
Yang Wei 20 China 2000 10-01-00 Gymnastics 1 1 0 2
Yordan Yovchev 27 Bulgaria 2000 10-01-00 Gymnastics 0 0 2 2
Elisa Di Francisca 29 Italy 2012 08-12-12 Fencing 2 0 0 2
Arianna Errigo 24 Italy 2012 08-12-12 Fencing 1 1 0 2
Diego Occhiuzzi 31 Italy 2012 08-12-12 Fencing 0 1 1 2
Sun Yujie 19 China 2012 08-12-12 Fencing 1 0 1 2
Valentina Vezzali 38 Italy 2012 08-12-12 Fencing 1 0 1 2
Stefano Carozzo 29 Italy 2008 08-24-08 Fencing 0 0 2 2
Margherita Granbassi 28 Italy 2008 08-24-08 Fencing 0 0 2 2
Sada Jacobson 25 United States 2008 08-24-08 Fencing 0 1 1 2
Fabrice Jeannet 27 France 2008 08-24-08 Fencing 1 1 0 2
Nicolas Lopez 27 France 2008 08-24-08 Fencing 1 1 0 2
Matteo Tagliariol 25 Italy 2008 08-24-08 Fencing 1 0 1 2
Valentina Vezzali 34 Italy 2008 08-24-08 Fencing 1 0 1 2
Becca Ward 18 United States 2008 08-24-08 Fencing 0 0 2 2
Mariel Zagunis 23 United States 2008 08-24-08 Fencing 1 0 1 2
Andrea Cassarà 20 Italy 2004 08-29-04 Fencing 1 0 1 2
Laura Flessel-Colovic 32 France 2004 08-29-04 Fencing 0 1 1 2
Aldo Montano 25 Italy 2004 08-29-04 Fencing 1 1 0 2
Maureen Nisima 23 France 2004 08-29-04 Fencing 0 0 2 2
Salvatore Sanzo 28 Italy 2004 08-29-04 Fencing 1 1 0 2
Mathieu Gourdain 26 France 2000 10-01-00 Fencing 0 2 0 2
Gianna Hablützel-Bürki 30 Switzerland 2000 10-01-00 Fencing 0 2 0 2
Rita König 23 Germany 2000 10-01-00 Fencing 0 1 1 2
Wiradech Kothny 21 Germany 2000 10-01-00 Fencing 0 0 2 2
Hugues Obry 27 France 2000 10-01-00 Fencing 0 2 0 2

SPARK SQL SAMPLE QUERY PROCESSING PROBLEM

The Transactions dataset should have the following attributes for each transaction:
TransID: unique sequential number (integer) from 1 to 5,000,000 (the file has 5M transactions)
CustID: References one of the customer IDs, i.e., from 1 to 50,000 (on Avg. a customer has 100 trans.)
TransTotal: random number (float) between 10 and 1000
TransNumItems: random number (integer) between 1 and 10
TransDesc: random text of characters of length between 20 and 50

--------------------------------------------------------------------------------------------

1) T1: Filter out (drop) the transactions from T whose total amount is less than $200
2) T2: Over T1, group the transactions by the Number of Items it has, and for each group
calculate the sum of total amounts, the average of total amounts, the min and the max of
the total amounts.
3) Report back T2 to the client side.
4) T3: Over T1, group the transactions by customer ID, and for each group report the
customer ID, and the transactions’ count.
5) T4: Filter out (drop) the transactions from T whose total amount is less than $600
6) T5: Over T4, group the transactions by customer ID, and for each group report the
customer ID, and the transactions’ count.


Queries in spark sql:


Project3:
scala> import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.SparkSession
scala> import spark.implicits._
import spark.implicits._
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

1)
scala> val input = sqlContext.read.json("t.json")
input: org.apache.spark.sql.DataFrame = [cid: bigint, desc: string ... 3 more fields]
scala> input.registerTempTable("trans")
warning: there was one deprecation warning; re-run with -deprecation for details

scala> val result = sqlContext.sql("SELECT * from trans where ttotal < 200")
result: org.apache.spark.sql.DataFrame = [cid: bigint, desc: string ... 3 more fields]
scala> result.show()
+-----+-------------------+----+------+------+
| cid| desc| tid|titems|ttotal|
+-----+-------------------+----+------+------+
|10111|computeraccessories| 112| 3| 150|
|10111|computeraccessories| 114| 5| 125|
|10111|computeraccessories| 116| 4| 180|
|10114|computeraccessories| 118| 3| 180|
|10115|computeraccessories| 119| 5| 100|
|10112|computeraccessories|1114| 5| 50|
|10112|computeraccessories|1115| 2| 150|

2)
scala> val result = sqlContext.sql("SELECT * from trans where ttotal < 200")
result: org.apache.spark.sql.DataFrame = [cid: bigint, desc: string ... 3 more fields]
scala> result.groupBy("titems").sum("ttotal").show()
[Stage 53:=======================> (42 + 1) / 100[Stage

53:============================> (52 + 2) / 100[Stage
53:====================================> (67 + 1) / 100[Stage
53:==========================================> (78 + 1) / 100[Stage
53:==================================================> (92 + 1) /
100 [Stage
55:=====================================> (50 + 1) / 75[Stage
55:==========================================> (57 + 1) / 75[Stage
55:===================================================> (69 + 1) /
75 +------+-----------+
|titems|sum(ttotal)|
+------+-----------+
| 5| 275|
| 3| 330|
| 2| 150|
| 4| 180|
+------+-----------+

scala> result.groupBy("titems").min("ttotal").show()
+------+-----------+
|titems|min(ttotal)|
+------+-----------+
| 5| 50|
| 3| 150|
| 2| 150|
| 4| 180|

scala> result.groupBy("titems").max("ttotal").show()
+------+-----------+
|titems|max(ttotal)|
+------+-----------+
| 5| 125|
| 3| 180|
| 2| 150|
| 4| 180|

3)
4) scala> result.groupBy("cid").count().show()
[Stage 63:===================================> (64 + 1) / 100[Stage
63:===========================================> (79 + 1) / 100[Stage
63:==================================================> (92 + 1) /
100 [Stage
65:===========================================> (58 + 1) / 75[Stage
65:=====================================================> (72 + 1) /
75 +-----+-----+
| cid|count|
+-----+-----+
|10112| 2|
|10114| 1|

|10111| 3|
|10115| 1|

5)
scala> val result3=result2.filter(result2("ttotal")<600)
result3: org.apache.spark.sql.Dataset[org.apache.spark.sql.Row] = [cid: bigint, desc: string ... 3 more
fields]
scala> result3.show
+-----+-------------------+----+------+------+
| cid| desc| tid|titems|ttotal|
+-----+-------------------+----+------+------+
|10111|computeraccessories| 111| 5| 500|
|10111|computeraccessories| 112| 3| 150|
|10112|computeraccessories| 113| 4| 200|
|10111|computeraccessories| 114| 5| 125|
|10112|computeraccessories| 115| 5| 400|
|10111|computeraccessories| 116| 4| 180|
|10113|computeraccessories| 117| 5| 500|
|10114|computeraccessories| 118| 3| 180|
|10115|computeraccessories| 119| 5| 100|
|10114|computeraccessories|1111| 3| 500|
|10112|computeraccessories|1114| 5| 50|
|10112|computeraccessories|1115| 2| 150|

6)
result3.groupBy("cid").count().show()
scala> result3.groupBy("cid").count().show()
[Stage 11:============================> (10 +[Stage
11:====================================> (13 +[Stage
11:==========================================> (15 +[Stage
11:=====================================================> (19
+ [Stage 13:============> (23 +
[Stage 13:================> (30 + [Stage
13:=====================> (39 + [Stage
13:========================> (45 + [Stage
13:===========================> (50 + [Stage
13:==============================> (56 + [Stage
13:===================================> (65 + [Stage
13:========================================> (73 + [Stage
13:============================================> (81 + [Stage
13:===================================================> (93 + [Stage
13:=====================================================> (98
+ [Stage
15:===================================> (47 +[Stage
15:============================================> (60 +[Stage
15:=====================================================> (71
+ +-----+-----+

| cid|count|
+-----+-----+
|10112| 4|
|10114| 2|
|10111| 4|
|10113| 1|
|10115| 1|

+-----+-----+