Randomizing Data In SQL

Randomizing Data In SQL

Generate randomized test/train datasets in SQL using NASA JPL dataset. One can easily use the rand() function to generate random floating point values between 0 and 1.

Sequel can be used to fetch random records from a given dataset. One can easily use the rand() function to generate random floating point values between 0 and 1. I recently wrote about how to generate test data using just SQL. With this function, a lot of useful things can be done. For example,

  • Disordering a dataset (or randomly ordering a dataset)
  • Generating a random number & a random number within a given range
  • Splitting a dataset into two or more parts (for testing & training)
  • Generating random strings (when used in conjunction with md5()

There are a lot of other interesting use cases where rand() fits in. For the scope of this writeup, we’ll use a planet & satellite dataset hosted by Devstronomy. Here’s the DDL for the two tables —

CREATE TABLE `l_planet` (
      `id` int(11) NOT NULL DEFAULT '0',
      `name` varchar(255) CHARACTER SET utf8 NOT NULL,
      `mass` decimal(16,6) NOT NULL,
      `diameter` decimal(16,6) NOT NULL,
      `density` decimal(16,6) NOT NULL,
      `gravity` decimal(16,6) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

    CREATE TABLE `l_satellite` (
      `id` int(11) NOT NULL DEFAULT '0',
      `planet_id` int(11) NOT NULL,
      `name` varchar(255) CHARACTER SET utf8 NOT NULL,
      `radius` decimal(16,6) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Picking a Random Satellite from a Random Planet

A simple example showing the use of rand() in a subquery — in this example, the purpose is to pick a random satellite from a random planet every time the query is run. This, however, doesn’t guarantee that two subsequent results of this query aren’t the same. The query obviously doesn’t have any memory of what the result on the last run was.

select * 
      from devstronomy.l_satellite 
     where planet_id = (select id 
                          from devstronomy.l_planet 
                         order by rand() 
                        limit 1) 
     order by rand() 
     limit 1;

random sql software-development analytics mysql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Offshore Software Development - Best Practices

To make the most out of the benefits of offshore software development, you should understand the crucial factors that affect offshore development.

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Wrestling Betting Software Development | WWE Betting Software Developers

Wrestling betting software development solutions from expert Wrestling betting app and software development company delivers ultimate betting experience. Chat with us.