Step 3: Develop Data

The method of finding out the 10 top-rated movies is as follows: Calculate the total score of each movie and the number of the users who participate in scoring the movies, filter out the movies that are scored by less than three users, and then return the movie names, average scores, and participant quantity.

1. Log in to the DataArts Studio console.

   For details, see Accessing the DataArts Studio Instance Console.

2. On the DataArts Studio console, locate a workspace and click DataArts Factory.
3. Create a DWS SQL script used to create data tables by entering DWS SQL statements in the editor.
   Figure 1 Creating a script
   

4. In the displayed SQL editor, set key parameters.

   Table 1 Key parameters

   Parameter	Description
   Connection	DWS data connection created in Step 5
   Database	Database created in Step 7

   Figure 2 Key parameters
   
   Enter the following SQL statements and click Execute to obtain the top 10 rated movies from the movies_item and ratings_item tables and save the result to the top_rating_movie table.

   SET
       SEARCH_PATH TO dgc;
   insert
       overwrite into top_rating_movie
   select
       a.movieTitle,
       b.ratings / b.rating_user_number as avg_rating,
       b.rating_user_number
   from
       movies_item a,
       (
           select
               movieId,
               sum(rating) ratings,
               count(1) as rating_user_number
           from
               ratings_item
           group by
               movieId
       ) b
   where
       rating_user_number > 3
       and a.movieId = b.movieId
   order by
       avg_rating desc
   limit
       10

   Figure 3 Script (top_rating_movie)
   

5. After debugging the script, click Save and Submit to submit the script and name it top_rating_movie. This script will be referenced later in Developing and Scheduling a Job.
6. After the script is saved and executed successfully, create a DWS SQL script by referring to step 3 and run the following SQL statements to view the data in the top_rating_movie table. You can also download or dump the table data by referring to Figure 4.

   SET SEARCH_PATH TO dgc;
   SELECT * FROM top_rating_movie

   Figure 4 Viewing the data in the top_rating_movie table
   

The method of finding out the 10 most frequently scored movies is as follows: Calculate the 10 most frequently scored movies whose average scores are higher than 3.5.

1. Log in to the DataArts Studio console.

   For details, see Accessing the DataArts Studio Instance Console.

2. On the DataArts Studio console, locate a workspace and click DataArts Factory.
3. Create a DWS SQL script used to create data tables by entering DWS SQL statements in the editor.
   Figure 5 Creating a script
   

4. In the displayed SQL editor, set key parameters.

   Table 2 Key parameters

   Parameter	Description
   Connection	DWS data connection created in Step 5
   Database	Database created in Step 7

   Figure 6 Key parameters
   
   Enter the following SQL statements and click Execute to obtain the top 10 most-watched movies from the movies_item and ratings_item tables and save the result to the top_active_movie table.

   SET
       SEARCH_PATH TO dgc;
   insert
       overwrite into top_active_movie
   select
       *
   from
       (
           select
               a.movieTitle,
               b.ratingSum / b.rating_user_number as avg_rating,
               b.rating_user_number
           from
               movies_item a,
               (
                   select
                       movieId,
                       sum(rating) ratingSum,
                       count(1) as rating_user_number
                   from
                       ratings_item
                   group by
                       movieId
               ) b
           where
               a.movieId = b.movieId
       ) t
   where
       t.avg_rating > 3.5
   order by
       rating_user_number desc
   limit
       10

   Figure 7 Script (top_active_movie)
   

5. After debugging the script, click Save and Submit to submit the script and name it top_active_movie. This script will be referenced later in Developing and Scheduling a Job.
6. After the script is saved and executed successfully, create a DWS SQL script by referring to step 3 and run the following SQL statements to view the data in the top_active_movie table. You can also download or dump the table data by referring to Figure 8.

   SET SEARCH_PATH TO dgc;
   SELECT * FROM top_active_movie

   Figure 8 Viewing the data in the top_active_movie table