Shulou(Shulou.com)06/01 Report--

Editor to share with you how to use SAP HANA Web-based Development tools for SQLScript exercises, I hope you will learn something after reading this article, let's discuss it together!

The database table contents provided through the csv file:

The format of links.csv:

Movies.csv format. A movie can have multiple styles (genres), separated by |

Ratings.csv:

Users give movie a score:

The label of tags.csv:movie

Exercise 1:

List the total number of records for the four tables:

Select 'links' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.LINKS "

Union all

Select 'movies' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.MOVIES "

Union all

Select 'ratings' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.RATINGS "

Union all

Select 'tags' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.TAGS "

Execution result:

Exercise 2: calculate a total of 9125 movies, how many art categories are included?

DO

BEGIN

DECLARE genreArray NVARCHAR (255) ARRAY

DECLARE tmp NVARCHAR (255)

DECLARE idx INTEGER

DECLARE sep NVARCHAR (1): ='|'

DECLARE CURSOR cur FOR SELECT DISTINCT "GENRES" FROM "MOVIELENS". Public.aa.movielens.hdb::data.MOVIES "

DECLARE genres NVARCHAR: =''

Idx: = 1

FOR cur_row AS cur () DO

SELECT cur_row. "GENRES" INTO genres FROM DUMMY

Tmp: =: genres

WHILE LOCATE (: tmp,:sep) > 0 DO

GenreArray [: idx]: = SUBSTR_BEFORE (: tmp,:sep)

Tmp: = SUBSTR_AFTER (: tmp,:sep)

Idx: =: idx + 1

END WHILE

GenreArray [: idx]: =: tmp

END FOR

GenreList = UNNEST (: genreArray) AS ("GENRE")

SELECT "GENRE" FROM: genreList GROUP BY "GENRE"

END

Implementation results, including a total of 18:

Exercise 3: calculate the total number of movies in each art category:

DO

BEGIN

DECLARE genreArray NVARCHAR (255) ARRAY

DECLARE tmp NVARCHAR (255)

DECLARE idx INTEGER

DECLARE sep NVARCHAR (1): ='|'

DECLARE CURSOR cur FOR SELECT DISTINCT "GENRES" FROM "MOVIELENS". Public.aa.movielens.hdb::data.MOVIES "

DECLARE genres NVARCHAR: =''

Idx: = 1

FOR cur_row AS cur () DO

SELECT cur_row. "GENRES" INTO genres FROM DUMMY

Tmp: =: genres

WHILE LOCATE (: tmp,:sep) > 0 DO

GenreArray [: idx]: = SUBSTR_BEFORE (: tmp,:sep)

Tmp: = SUBSTR_AFTER (: tmp,:sep)

Idx: =: idx + 1

END WHILE

GenreArray [: idx]: =: tmp

END FOR

GenreList = UNNEST (: genreArray) AS ("GENRE")

SELECT "GENRE", count (1) FROM: genreList GROUP BY "GENRE"

END

Exercise 4: list the number of styles contained in each movie:

SELECT

"MOVIEID"

, "TITLE"

, OCCURRENCES_REGEXPR ('[|]'IN GENRES) + 1 "GENRE_COUNT"

, "GENRES"

FROM "MOVIELENS". "public.aa.movielens.hdb::data.MOVIES"

ORDER BY "GENRE_COUNT" ASC

Exercise 5: list the style distribution of each movie

SELECT

"GENRE_COUNT"

, COUNT (1)

FROM (

SELECT

OCCURRENCES_REGEXPR ('[|]'IN "GENRES") + 1 "GENRE_COUNT"

FROM "MOVIELENS". "public.aa.movielens.hdb::data.MOVIES"

)

GROUP BY "GENRE_COUNT" ORDER BY "GENRE_COUNT"

For example, there are at least one style of film, 2793 films, 3039 films of two styles, and so on.

Exercise 6: calculate the rating distribution of movie

SELECT DISTINCT

MIN ("RATING_COUNT") OVER () AS "MIN"

MAX ("RATING_COUNT") OVER () AS "MAX"

AVG ("RATING_COUNT") OVER () AS "AVG"

SUM ("RATING_COUNT") OVER () AS "SUM"

MEDIAN ("RATING_COUNT") OVER () AS "MEDIAN"

STDDEV ("RATING_COUNT") OVER () AS "STDDEV"

COUNT (*) OVER () AS "CATEGORY_COUNT"

FROM (

SELECT "MOVIEID", COUNT (1) as "RATING_COUNT"

FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"

GROUP BY "MOVIEID"

)

GROUP BY "RATING_COUNT"

Details:

SELECT "RATING_COUNT", COUNT (1) as "MOVIE_COUNT"

FROM (

SELECT "MOVIEID", COUNT (1) as "RATING_COUNT"

FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"

GROUP BY "MOVIEID"

)

GROUP BY "RATING_COUNT" ORDER BY "RATING_COUNT" asc

For example, there are 397 movies with 5 votes cast by users.

Exercise 7: count users' votes

SELECT "RATING_COUNT", COUNT (1) as "USER_COUNT"

FROM (

SELECT "USERID", COUNT (1) as "RATING_COUNT"

FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"

GROUP BY "USERID"

)

GROUP BY "RATING_COUNT" ORDER BY 1 DESC

One user cast 2391 votes and one user cast 1868 votes:

Exercise 8: count users' voting scores

SELECT "RATING", COUNT (1) as "RATING_COUNT"

FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"

GROUP BY "RATING" ORDER BY 1 DESC

15095 users voted, with a score of 5.

After reading this article, I believe you have some understanding of "how to use SAP HANA Web-based Development tools for SQLScript exercises". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.