如何使用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 allselect ‘movies’ as “table name”, count(1) as “row count” from “MOVIELENS”.”public.aa.movielens.hdb::data.MOVIES”union allselect ‘ratings’ as “table name”, count(1) as “row count” from “MOVIELENS”.”public.aa.movielens.hdb::data.RATINGS”union allselect ‘tags’ as “table name”, count(1) as “row count” from “MOVIELENS”.”public.aa.movielens.hdb::data.TAGS”;执行结果：DOBEGINDECLARE 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() DOSELECT cur_row.”GENRES” INTO genres FROM DUMMY;tmp := :genres;WHILE LOCATE(:tmp,:sep) > 0 DOgenreArray[: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种：DOBEGINDECLARE 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() DOSELECT cur_row.”GENRES” INTO genres FROM DUMMY;tmp := :genres;WHILE LOCATE(:tmp,:sep) > 0 DOgenreArray[: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;SELECT”MOVIEID”, “TITLE”, OCCURRENCES_REGEXPR(‘[|]’ IN GENRES) + 1 “GENRE_COUNT”, “GENRES”FROM “MOVIELENS”.”public.aa.movielens.hdb::data.MOVIES”ORDER BY “GENRE_COUNT” ASC;SELECT”GENRE_COUNT”, COUNT(1)FROM (SELECTOCCURRENCES_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部，等等。SELECT DISTINCTMIN(“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 “RATIN 香港云主机G_COUNT” ORDER BY “RATING_COUNT” asc;比如有397部电影的用户投票数为5票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票：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练习”有了一定的了解，如果想了解更多相关知识，欢迎关注开发云行业资讯频道，感谢各位的阅读！

相关推荐: 如何使用Python爬取天猫店铺联系方式

这篇文章将为大家详细讲解有关如何使用Python爬取天猫店铺联系方式，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。1、获取商家信息很多初创公司为了获得客户资源，通常会有大量的可拜访客户信息的需求。如果是在以前，…