How to Choose Between NUMERIC, DOUBLE PRECISION, and REAL Number Types for a Column

Help Center
1

Introduction

In gaming analytics, choosing the correct numeric data type significantly impacts data precision and query performance. This article will help you select the appropriate data type—NUMERIC, DOUBLE PRECISION, or REAL—for different use cases in the context of the Ultra Fast Gaming data.

TL;DR

  • NUMERIC :
    * Precision : 28-29 significant digits.
    * Storage Size : Variable depending on precision.
    * Best Use : Financial transactions or any scenario where precision is critical.
  • DOUBLE PRECISION :
    * Precision : 15-16 digits.
    * Storage Size : 8 bytes.
    * Best Use : Statistical computations that can tolerate minor precision errors.
  • REAL :
    * Precision : 7 digits.
    * Storage Size : 4 bytes.
    * Best Use : High-speed calculations where storage and performance are more important than precision.

Step-by-Step Guide

All the example SQL code uses the Ultra Fast Gaming data set. To familiarize
yourself with this data set, visit this link: Ultra Fast Gaming Firebolt
Sample Dataset.

Step 1: Using NUMERIC for Game Versions

The games table stores the versions of different games, which often include decimal points. NUMERIC is ideal for ensuring these values are stored with exact precision.

-- Example for storing game versions with high precision
    CREATE DIMENSION TABLE IF NOT EXISTS games (
    GameID INTEGER,
    Title TEXT,
    Version NUMERIC(5, 2), -- Stores game versions accurately
    LaunchDate DATE,
    Author TEXT
    ) PRIMARY INDEX GameID;

Step 2: Using DOUBLE PRECISION for Player Attributes

The players table can use DOUBLE PRECISION to store player attributes like probabilities, which require significant precision but can tolerate minor errors.

-- Using DOUBLE PRECISION for player attributes
    CREATE DIMENSION TABLE IF NOT EXISTS players (
    PlayerID INTEGER,
    InternalProbabilityToWin DOUBLE PRECISION, -- Stores probability of winning
    AgeCategory TEXT,
    RegisteredOn DATE
    ) PRIMARY INDEX PlayerID;

Step 3: Using REAL for Gameplay Statistics

The playstats table stores gameplay metrics that require efficient computation and where storage matters, such as tracking speed and playtime.

-- Storing real-time gameplay metrics like speed and playtime
    CREATE FACT TABLE IF NOT EXISTS playstats (
    GameID INTEGER,
    PlayerID INTEGER,
    CurrentSpeed REAL, -- Suitable for game speeds with quick computation
    CurrentPlayTime BIGINT,
    CurrentScore BIGINT,
    TournamentID INTEGER
    ) PRIMARY INDEX GameID, PlayerID, TournamentID;

Step 4: Comparing the Types

  • NUMERIC : No tolerance for error in financial data.
  • DOUBLE PRECISION : Best for voluminous data needing fast computation with moderate precision.
  • REAL : Optimal for metrics requiring the highest performance with acceptable precision compromises.

Step 5: Decision-Making Criteria

Consider the following when choosing a numeric type:

  • Precision Needs : Use NUMERIC for financial and other high-precision needs.
  • Performance Needs : Opt for REAL in performance-critical applications like real-time analytics.
  • Balanced Needs : Choose DOUBLE PRECISION for general statistical data.

Full Example Code

CREATE DIMENSION TABLE IF NOT EXISTS games (
    GameID INTEGER,
    Title TEXT,
    Version NUMERIC(5, 2),
    LaunchDate DATE,
    Author TEXT
    ) PRIMARY INDEX GameID;
    
    CREATE DIMENSION TABLE IF NOT EXISTS players (
    PlayerID INTEGER,
    InternalProbabilityToWin DOUBLE PRECISION,
    AgeCategory TEXT,
    RegisteredOn DATE
    ) PRIMARY INDEX PlayerID;
    
    CREATE FACT TABLE IF NOT EXISTS playstats (
    GameID INTEGER,
    PlayerID INTEGER,
    CurrentSpeed REAL,
    CurrentPlayTime BIGINT,
    CurrentScore BIGINT,
    TournamentID INTEGER
    ) PRIMARY INDEX GameID, PlayerID, TournamentID;