Home
Analogues
This section contains 1010data analogues for Excel and SQL users.
1010data for SQL users
1010data users have backgrounds using different programming languages, databases, and analytical tools. One of the most widely used tools for data is SQL. This section of the 1010data Cookbook was created to help the users with SQL background learn how to complete tasks in 1010data. Each analogue will describe a problem that the user knows how to complete in SQL, and then show how to solve the problem in 1010data.
Introduction

Analogues
This section contains 1010data analogues for Excel and SQL users.
- 1010data for Excel users
  1010data can solve the same problems that you are used to solving in Excel in one or more ways. Sometimes there are direct translations between the two, and sometimes a unique solution is needed in order to arrive at the same result.
- 1010data for SQL users
  1010data users have backgrounds using different programming languages, databases, and analytical tools. One of the most widely used tools for data is SQL. This section of the 1010data Cookbook was created to help the users with SQL background learn how to complete tasks in 1010data. Each analogue will describe a problem that the user knows how to complete in SQL, and then show how to solve the problem in 1010data.
  - Introduction
    - Workflow
    - Data Types
      The data types offered in both SQL and 1010data differ. It is important to know how these data types should be converted in between systems.
  - Function comparisons
  - Counting occurrences
    Based on specified groups in your data, you can determine how many times pairs of values occur together.
  - Counting based on conditions
    You can determine how many times a specified value occurs in a table based on predetermined and conditional requirements.
  - Calculating group specific averages
    Instead of calculating one overall average, you can calculate smaller averages based on criteria specific groups.
  - Selecting based on rank
    You can select values based on certain criteria, for example if their rank puts them in a certain range.
  - Time comparison analysis by groups
    You can compare aggregated data for two different time periods, even if that data is contained in the same column.
  - Performing an inner join
    An INNER JOIN in SQL is similar to a link and select in 1010data.
  - Performing a left or right join
    With some manipulation, a simple link in 1010data can produce the same results as a LEFT JOIN or a RIGHT JOIN in SQL.
  - Performing a union
    A UNION in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. During this union, all duplicates are removed.
  - Performing a union all
    A UNION ALL in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. With this variation of UNION all rows, including duplicates, are retained in the final worksheet.
  - Denormalizing tables
    In SQL it is very common to have smaller tables that are used as reference tables to a larger data set. This allows the data to be stored easier and accessed faster. However, if you want to perform any calculations that involve these reference tables in addition to your main table, you must first combine them, or denormalize them.
  - Transposing a table
    In order to transpose a table using SQL, multiple operations are required. Using 1010data, you can transpose a table with one basic operation.

Introduction

SQL and 1010data store their data in very different ways, therefore the way you treat your data in each language needs to be different as well. SQL is a relational database, and the data is stored in rows, allowing for data to be easily added and removed. Contrastingly, 1010data is an analysis-first database, and the data is stored in columns, allowing for various analyses to be executed over large amounts of data, more efficiently in many cases.

When looking at each analogue, you might notice that there are multiple 1010data solutions for one SQL solution. Each analogue attempts to illustrate a direct translation from SQL to 1010data, however sometimes this direct translation might not be the most efficient solution for 1010data's platform. Therefore, the most efficient solution will be listed as the main solution, while the alternate solutions shown contain a different approach to solve the problem. It is also important to note that each SQL solution is created using MySQL, and therefore there might be alternative ways to solve the problem using different functions and operations that are not available in MySQL.

When using SQL, you need to think about what you want your end result to be, and which steps will get you there. Most often, you will have to create your query in such a way that all commands are nested within each other, using a series of JOIN's and/or UNION's, or you will need to create intermediate tables containing your results at each point in your query in order to use these results in the next step. You cannot simply enter in commands as you progress, building on the query as you go. And if you decide to make a change in your query at any point in time, you need to resubmit your whole query from the beginning with the change added. With 1010data, you're able to build on your query as you go. This is because 1010data preserves the state of your session and stores the results of your queries in your cache. Also, this eliminates the need to materialize intermediate tables to use in your query.