如何使用SAP HANA Web-based Development工具進行SQLScript練習

小編給大家分享一下如何使用SAP HANA Web-based Development工具進行SQLScript練習，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

通過csv文件提供的數據庫表內容：

links.csv的格式：

movies.csv格式，一個movie可以有多種風格(genres)，通過|分隔：

ratings.csv:

用戶給movie打得分：

tags.csv：movie的標簽

練習一：

列出四張表的總記錄數：

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";

執行結果：

練習2：計算總共9125部電影，一共包含多少藝術類別？

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 (255) := '';

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;

執行結果，總共包含18種：

練習3：計算每種藝術類別總共包含多少部電影：

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 (255) := '';

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;

練習4：列出每部電影包含的風格數目：

SELECT

"MOVIEID"

, "TITLE"

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

, "GENRES"

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

ORDER BY "GENRE_COUNT" ASC;

練習5：羅列出每部電影的風格分布情況

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";

比如至少擁有1個風格的電影，有2793部，2個風格的電影有3039部，等等。

練習6：計算movie的rating分布情況

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";

明細情況：

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;

比如有397部電影的用戶投票數為5票

練習7：統計用戶投票情況

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;

有一位用戶投了2391票，一位用戶投了1868票：

練習8：統計用戶投票得分情況

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

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

GROUP BY "RATING" ORDER BY 1 DESC;

有15095份用戶投票，打的分數是5分

看完了這篇文章，相信你對“如何使用SAP HANA Web-based Development工具進行SQLScript練習”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！