Category: 3 SPL Desktop Analysis

Here we are talking about analysis of consecutively rising stocks. To avoid ambiguity, we specify that the number of consecutively rising dates includes the initial date. For example, rising consecutively for 5 days means that a stock rises in a row in the 5 dates. Yet actually there are only 4 price increases that begin on the 2nd date. Data structure and sample data: CODE is stock code, DT is transaction date, and CL is closing price. 1. Get a stock’s largest number of consecutively rising dates Suppose the code of target stock is 100046, we handle the computing task

The order data table records sales order information for the past few years. The employee information table records basic information for all employees. 1. Aggregate and join into a wide table Aggregate the original order data by year. Then, join it with the employee information table to create a table like the one below for further data analysis. In Excel, multiple steps need to be performed manually. (1) Summarize the annual sales of each salesperson. Since there is no year in the original order data, only the order date, it needs to use the YEAR() function to extract year. Then

Data structure Country table world.country: Code is the country code; Name is the country name; Population is the country population. Country language table world.countrylanguage: CountryCode is the country code; Language is the language; IsOfficial is whether it is an official language (T represents an official language); Percentage is the percentage of the population using the language. 1. Query for the country with the most official languages, its population, and the number of official languages It can be seen from the example data that each country may use multiple languages, including both official and non-official languages. SQL statement: While the window

This article is a continuation of Data Analysis Programming from SQL to SPL: User Event Statistics Continue to use the user behavior table actions, which records the occurrence time of 9 types of events for each user: Download test data 3. Search for users who ranked in the top 500 for activity for four consecutive days The more events a user has per day, the more active they are considered to be. Therefore, the activity ranking is the ranking of the number of events. First, group and aggregate the number of events by date and user. Then, find the top

The following is part of the data from the simplified user behavior table actions, which records the occurrence time of 9 types of events for each user: 1. Calculate the number of sessions for each user A session is considered over if a user does not take action for more than 2 days, or does not take action within 2 hours after an e8 event. The basic idea to solve the problem is to first group by user, then count the number of sessions when the session end condition is satisfied in each user’s event set, and finally add 1

Data structure and sample data: In which, sid is the stock code, tdate is the trading date, and close is the closing price. 1. Calculate the 12-day EMA of the daily closing price The n-day EMA of a stock on its first listing day is the closing price of that day. The n-day EMA = (the previous day’s n-day EMA * (n-1) + the current day’s closing price * 2)/ (n+1). When n is 12, it is called the 12-day EMA, abbreviated as e12. Now we need to calculate the 12-day EMA of stock 600036 for each trading day. SQL

Data structure and sample data: In which, sid is the stock code, tdate is the transaction date, and close is the closing price. 1. Find the stock price crossing above its median The median stock price on day T refers to the median of the closing prices from the listing date to day T. The stock price crossing above its median on day T means that the closing price on day T is greater than the median on day T, and the closing price on day T-1 is less than the median on day T-1, or the closing price on

Here we are talking about analysis of consecutively rising stocks. To avoid ambiguity, we specify that the number of consecutively rising dates includes the initial date. For example, rising consecutively for 5 days means that a stock rises in a row in the 5 dates. Yet actually there are only 4 price increases that begin on the 2nd date. Data structure and sample data: CODE is stock code, DT is transaction date, and CL is closing price. 1. Get the largest number of consecutively rising dates for a stock Suppose the code of target stock is 100046, we have the

The puzzles facing Excel data analysts In the world of data, data analysts who work with Excel are like a group of “knights holding the ‘Excalibur’”. Relying on Excel’s powerful interactive ability and ease of use, Excel knights cut through obstacles easily and perform data analysis efficiently. However, potential challenges are surging continuously in the said world. As the scale of data continues to grow and the requirements for data analysis are increasingly complex, relying solely on the ‘Excalibur’ seems insufficient to cope with the ever-emerging challenges. Excel is good, but it seems powerless when faced with complex ‘data enemies.’

Exploratory data analysis is important Many data analysis tasks involve dynamic queries that require flexible analysis and judgment, such as user behavior analysis, product recommendations and inventory optimization based on ecommerce data, and risk assessment and customer categorization in finance industry. All of them are not fixed queries. Analysts need to gradually adjust the analytic operations according to data performance variation so that they can dig up underlying patterns and anomalies. The dynamic analysis approach is the core of exploratory data analysis. Exploratory data analysis features the flexible guess-and-confirm mode. Unlike the fixed queries, exploratory analysis is a process of