Skip to content

This is the multi-page printable view of this section. .

Return to the regular view of this page.

Visualization Applet

Run data analysis & visualization, data app demo with Pigsty

pigsty-app.jpg

An Applet is a self-contained, data-driven mini-application that runs within the Pigsty infrastructure.

A typical Pigsty application includes at least one or all of the following components:

  • Graphical Interface (Grafana Dashboard definitions) located in the ui directory
  • Data Definitions (PostgreSQL DDL Files) located in the sql directory
  • Data Files (various resources that need to be downloaded) located in the data directory
  • Logic Scripts (scripts for executing various logic) located in the bin directory

Pigsty comes with several sample applications by default:

  • pglog: Analyzes PostgreSQL CSV log samples.
  • covid: Visualizes WHO COVID-19 data and allows you to check pandemic data by country.
  • isd: NOAA ISD, which provides access to meteorological observation records from 30,000 surface weather stations worldwide since 1901.

Structure

A Pigsty applet provides an installation script in its root directory: install or a related shortcut. You need to run this script as an admin user on the admin node to execute the installation. The installation script will detect the current environment (fetching METADB_URL, PIGSTY_HOME, GRAFANA_ENDPOINT, etc.) to complete the installation.

Typically, dashboards with the APP tag will be listed in the Pigsty Grafana homepage navigation under the Apps dropdown menu, and dashboards with both APP and OVERVIEW tags will be listed in the homepage panel navigation.

1 - Analyse CSVLOG Sample with the built-in PGLOG

Analyse CSVLOG Sample with the built-in PGLOG

PGLOG is a sample application included with Pigsty that uses the pglog.sample table in MetaDB as the data source. Simply populate this table with logs and access the corresponding Dashboard.

Pigsty provides some handy commands to fetch CSV logs and load them into the sample table. The following shortcut commands are available on the master node by default:

catlog  [node=localhost]  [date=today]   # Print CSV logs to standard output
pglog                                    # Load CSVLOG from standard input
pglog12                                  # Load CSVLOG in PG12 format
pglog13                                  # Load CSVLOG in PG13 format
pglog14                                  # Load CSVLOG in PG14 format (=pglog)

catlog | pglog                       # Analyze logs of the current node for today
catlog node-1 '2021-07-15' | pglog   # Analyze CSV logs of node-1 for 2021-07-15

Next, you can visit the following links to view sample log analysis dashboards:

pglog-overview.jpg

pglog-session.jpg

The catlog command fetches CSV database logs from a specific node for a specific date and writes them to stdout.

By default, catlog fetches logs of the current node for today, but you can specify the node and date via parameters.

By combining pglog and catlog, you can quickly fetch and analyze database CSV logs.

catlog | pglog                       # Analyze logs of the current node for today
catlog node-1 '2021-07-15' | pglog   # Analyze CSV logs of node-1 for 2021-07-15

2 - NOAA ISD Station

Fetch, Parse, Analyze, and Visualize Integrated Surface Weather Station Dataset

Including 30000 meteorology station, daily, sub-hourly observation records, from 1900-2023. https://github.com/Vonng/isd

ISD Overview

It is recommended to use with Pigsty, the battery-included PostgreSQL distribution with Grafana & echarts for visualization. It will setup everything for your with make all;

Otherwise, you’ll have to provide your own PostgreSQL instance, and setup grafana dashboards manually.

Quick Start

Clone this repo

git clone https://github.com/Vonng/isd.git; cd isd;

Prepare a PostgreSQL Instance

Export PGURL in your environment to specify the target postgres database:

# the default PGURL for pigsty's meta database, change that accordingly
export PGURL=postgres://dbuser_dba:[email protected]:5432/meta?sslmode=disable
psql "${PGURL}" -c 'SELECT 1'  # check if connection is ok

then init database schema with:

make sql              # setup postgres schema on target database

Get isd station metadata

The basic station metadata can be downloaded and loaded with:

make reload-station   # equivalent to get-station + load-station

Fetch and load isd.daily

To load isd.daily dataset, which is organized by yearly tarball files. You can download the raw data from noaa and parse with isd parser

make get-parser       # download parser binary from github, you can just build with: make build
make reload-daily     # download and reload latest daily data and re-calculates monthly/yearly data

Load Parsed Stable CSV Data

Or just load the pre-parsed stable part from GitHub. Which is well-formatted CSV that does not require an isd parser.

make get-stable       # download stable isd.daily dataset from Github
make load-stable      # load downloaded stable isd.daily dataset into database

More Data

There are two parts of isd datasets needs to be regularly updated: station metadata & isd.daily of the latest year, you can reload them with:

make reload           # reload-station + reload-daily

You can download and load isd.daily in a specific year with:

bin/get-daily  2022                   # get daily observation summary of a specific year (1900-2023)
bin/load-daily "${PGURL}" 2022    # load daily data of a specific year

You can also download and load isd.hourly in a specific year with:

bin/get-hourly  2022                  # get hourly observation record of a specific year (1900-2023)
bin/load-hourly "${PGURL}" 2022   # load hourly data of a specific year

Data

Dataset

There are four official datasets

Dataset Sample Document Comments
ISD Hourly isd-hourly-sample.csv isd-hourly-document.pdf (Sub)Hour observation records
ISD Daily isd-daily-sample.csv isd-daily-format.txt Daily summary
ISD Monthly N/A isd-gsom-document.pdf Not used, Generate from isd.daily
ISD Yearly N/A isd-gsoy-document.pdf Not used, Generate from isd.daily

Daily Dataset

  • Tarball size 2.8GB (until 2023-06-24)
  • Table size 24GB, Index size 6GB, Total size in PostgreSQL = 30GB
  • If timescaledb compression is used, it will be compressed to around 4.5GB

Hourly dataset

  • Tarball size 117GB
  • Table size 1TB+ , Index size 600GB+

Schema 

CREATE TABLE isd.station
(
    station    VARCHAR(12) PRIMARY KEY,
    usaf       VARCHAR(6) GENERATED ALWAYS AS (substring(station, 1, 6)) STORED,
    wban       VARCHAR(5) GENERATED ALWAYS AS (substring(station, 7, 5)) STORED,
    name       VARCHAR(32),
    country    VARCHAR(2),
    province   VARCHAR(2),
    icao       VARCHAR(4),
    location   GEOMETRY(POINT),
    longitude  NUMERIC GENERATED ALWAYS AS (Round(ST_X(location)::NUMERIC, 6)) STORED,
    latitude   NUMERIC GENERATED ALWAYS AS (Round(ST_Y(location)::NUMERIC, 6)) STORED,
    elevation  NUMERIC,
    period     daterange,
    begin_date DATE GENERATED ALWAYS AS (lower(period)) STORED,
    end_date   DATE GENERATED ALWAYS AS (upper(period)) STORED
);
CREATE TABLE IF NOT EXISTS isd.daily
(
    station     VARCHAR(12) NOT NULL, -- station number 6USAF+5WBAN
    ts          DATE        NOT NULL, -- observation date
    -- temperature & dew point
    temp_mean   NUMERIC(3, 1),        -- mean temperature ℃
    temp_min    NUMERIC(3, 1),        -- min temperature ℃
    temp_max    NUMERIC(3, 1),        -- max temperature ℃
    dewp_mean   NUMERIC(3, 1),        -- mean dew point ℃
    -- pressure
    slp_mean    NUMERIC(5, 1),        -- sea level pressure (hPa)
    stp_mean    NUMERIC(5, 1),        -- station pressure (hPa)
    -- visible distance
    vis_mean    NUMERIC(6),           -- visible distance (m)
    -- wind speed
    wdsp_mean   NUMERIC(4, 1),        -- average wind speed (m/s)
    wdsp_max    NUMERIC(4, 1),        -- max wind speed (m/s)
    gust        NUMERIC(4, 1),        -- max wind gust (m/s)
    -- precipitation / snow depth
    prcp_mean   NUMERIC(5, 1),        -- precipitation (mm)
    prcp        NUMERIC(5, 1),        -- rectified precipitation (mm)
    sndp        NuMERIC(5, 1),        -- snow depth (mm)
    -- FRSHTT (Fog/Rain/Snow/Hail/Thunder/Tornado)
    is_foggy    BOOLEAN,              -- (F)og
    is_rainy    BOOLEAN,              -- (R)ain or Drizzle
    is_snowy    BOOLEAN,              -- (S)now or pellets
    is_hail     BOOLEAN,              -- (H)ail
    is_thunder  BOOLEAN,              -- (T)hunder
    is_tornado  BOOLEAN,              -- (T)ornado or Funnel Cloud
    -- record count
    temp_count  SMALLINT,             -- record count for temp
    dewp_count  SMALLINT,             -- record count for dew point
    slp_count   SMALLINT,             -- record count for sea level pressure
    stp_count   SMALLINT,             -- record count for station pressure
    wdsp_count  SMALLINT,             -- record count for wind speed
    visib_count SMALLINT,             -- record count for visible distance
    -- temp marks
    temp_min_f  BOOLEAN,              -- aggregate min temperature
    temp_max_f  BOOLEAN,              -- aggregate max temperature
    prcp_flag   CHAR,                 -- precipitation flag: ABCDEFGHI
    PRIMARY KEY (station, ts)
); -- PARTITION BY RANGE (ts);
ISD Hourly 
CREATE TABLE IF NOT EXISTS isd.hourly
(
    station    VARCHAR(12) NOT NULL, -- station id
    ts         TIMESTAMP   NOT NULL, -- timestamp
    -- air
    temp       NUMERIC(3, 1),        -- [-93.2,+61.8]
    dewp       NUMERIC(3, 1),        -- [-98.2,+36.8]
    slp        NUMERIC(5, 1),        -- [8600,10900]
    stp        NUMERIC(5, 1),        -- [4500,10900]
    vis        NUMERIC(6),           -- [0,160000]
    -- wind
    wd_angle   NUMERIC(3),           -- [1,360]
    wd_speed   NUMERIC(4, 1),        -- [0,90]
    wd_gust    NUMERIC(4, 1),        -- [0,110]
    wd_code    VARCHAR(1),           -- code that denotes the character of the WIND-OBSERVATION.
    -- cloud
    cld_height NUMERIC(5),           -- [0,22000]
    cld_code   VARCHAR(2),           -- cloud code
    -- water
    sndp       NUMERIC(5, 1),        -- mm snow
    prcp       NUMERIC(5, 1),        -- mm precipitation
    prcp_hour  NUMERIC(2),           -- precipitation duration in hour
    prcp_code  VARCHAR(1),           -- precipitation type code
    -- sky
    mw_code    VARCHAR(2),           -- manual weather observation code
    aw_code    VARCHAR(2),           -- auto weather observation code
    pw_code    VARCHAR(1),           -- weather code of past period of time
    pw_hour    NUMERIC(2),           -- duration of pw_code period
    -- misc
    -- remark     TEXT,
    -- eqd        TEXT,
    data       JSONB                 -- extra data
) PARTITION BY RANGE (ts);

Parser

There are two parsers: isdd and isdh, which takes noaa original yearly tarball as input, generate CSV as output (which could be directly consumed by PostgreSQL COPY command).

NAME
        isd -- Intergrated Surface Dataset Parser

SYNOPSIS
        isd daily   [-i <input|stdin>] [-o <output|stout>] [-v]
        isd hourly  [-i <input|stdin>] [-o <output|stout>] [-v] [-d raw|ts-first|hour-first]

DESCRIPTION
        The isd program takes noaa isd daily/hourly raw tarball data as input.
        and generate parsed data in csv format as output. Works in pipe mode

        cat data/daily/2023.tar.gz | bin/isd daily -v | psql ${PGURL} -AXtwqc "COPY isd.daily FROM STDIN CSV;"

        isd daily  -v -i data/daily/2023.tar.gz  | psql ${PGURL} -AXtwqc "COPY isd.daily FROM STDIN CSV;"
        isd hourly -v -i data/hourly/2023.tar.gz | psql ${PGURL} -AXtwqc "COPY isd.hourly FROM STDIN CSV;"

OPTIONS
        -i  <input>     input file, stdin by default
        -o  <output>    output file, stdout by default
        -p  <profpath>  pprof file path, enable if specified
        -d              de-duplicate rows for hourly dataset (raw, ts-first, hour-first)
        -v              verbose mode
        -h              print help

UI

ISD Overview

Show all stations on a world map.

isd-overview.jpg

ISD Country

Show all stations among a country.

isd-country.jpg

ISD Station

Visualize station metadata and daily/monthly/yearly summary

ISD Station Dashboard

isd-station.jpg

ISD Detail

Visualize hourly observation raw metrics.

ISD Station Dashboard

isd-detail.jpg

License

MIT License

3 - WHO COVID-19 Data Analysis

Pigsty built-in application which visualize WHO covid-19 data

The on-line demo：https://demo.pigsty.cc/d/covid-overview

Installation

在管理节点上进入应用目录，执行make以完成安装。

make            # 如果本地数据可用
make all        # 完整安装，从WHO官网下载数据
makd reload     # 重新下载并加载最新数据

其他一些子任务：

make reload     # download latest data and pour it again
make ui         # install grafana dashboards
make sql        # install database schemas
make download   # download latest data
make load       # load downloaded data into database
make reload     # download latest data and pour it into database

Dashboards

4 - Solve the 24-Point Card Game with One SQL Statement

A PostgreSQL solution that returns one expression for the 24-point card game using a single SQL statement.

Problem

The challenge is described in Database Programming Contest: Calculate 24 with One SQL Statement.

A cards table has an auto-incrementing id and four columns, c1 through c4; each card is a random integer from 1 to 10. One SQL statement must return a valid arithmetic expression whose result is 24, or NULL when no solution exists.

The rules allow only addition, subtraction, multiplication, division, and parentheses. Every input number must be used exactly once. Built-in database functions are allowed, but stored procedures, user-defined functions, and code blocks are not. The submitted SQL must be smaller than 10 KB and was evaluated on a 4-core, 32 GB server.

Prime-number lookup is the fastest general approach, but the complete lookup text is slightly larger than 10 KB. MySQL provides built-in COMPRESS and UNCOMPRESS; PostgreSQL uses the pgsql-gzip extension for the compressed variant. The following is the PostgreSQL solution.

Create Random Test Data 

CREATE SCHEMA poker24;
DROP TABLE IF EXISTS poker24.cards;
CREATE TABLE poker24.cards AS
SELECT i                   AS id,
       ceil(random() * 10) AS c1,
       ceil(random() * 10) AS c2,
       ceil(random() * 10) AS c3,
       ceil(random() * 10) AS c4
FROM generate_series(1, 1000000) i;

ALTER TABLE poker24.cards ADD PRIMARY KEY (id);

Prime-Encoded Lookup Solution

The core idea assigns each card value a prime number. The product of four primes is an order-independent key, so every solvable multiset can be joined to one precomputed expression in constant time.

EXPLAIN ANALYZE
WITH a(i, result) AS (
    SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
    FROM regexp_split_to_table('152:((1+1)+1)*8,156:(6*2)*(1+1),204:(7+1)*(2+1),228:((1*1)+2)*8,276:(9-1)*(2+1),348:(10+2)*(1+1),140:(4*3)*(1+1),220:(5+1)*(3+1),260:((1+1)+6)*3,340:((1*1)+7)*3,380:(8*3)+(1-1),460:(9+3)*(1+1),580:(10-(1+1))*3,196:((1+1)+4)*4,308:((1*1)+5)*4,364:(6*4)+(1-1),476:(7-(1*1))*4,532:(8+4)*(1+1),644:(9-1)*(4-1),812:((1+1)*10)+4,484:(5*5)-(1*1),572:(5-(1*1))*6,748:(7+5)*(1+1),836:(5-(1+1))*8,676:(6+6)*(1+1),988:(8*6)/(1+1),1196:((1+1)*9)+6,1972:((1+1)*7)+10,1444:((1+1)*8)+8,126:(4*2)*(2+1),198:(2+2)*(5+1),234:(6+2)*(2+1),306:(2+2)*(7-1),342:((2-1)+2)*8,414:((2+1)+9)*2,522:(10-2)*(2+1),150:(3*2)*(3+1),210:((2+1)+3)*4,330:(5+3)*(2+1),390:((2-1)+3)*6,510:(7*3)+(2+1),570:(8*3)*(2-1),690:(9*3)-(2+1),870:(10-(2*1))*3,294:(4+4)*(2+1),462:((2-1)+5)*4,546:(6*4)*(2-1),714:(7-(2-1))*4,798:(4-(2-1))*8,966:(9-(2+1))*4,1218:((2*1)*10)+4,726:(5*5)-(2-1),858:(5-(2-1))*6,1122:(7+5)*(2*1),1254:(5-(2*1))*8,1518:((2+1)*5)+9,1914:(10*2)+(5-1),1014:((2+1)*6)+6,1326:(7-(2+1))*6,1482:(6-(2+1))*8,1794:((2*1)*9)+6,2262:((2+1)*10)-6,1734:((7*7)-1)/2,1938:(8*2)+(7+1),2346:(9*2)+(7-1),2958:((2*1)*7)+10,2166:((2*1)*8)+8,2622:(9*8)/(2+1),3306:((8-1)*2)+10,250:(3+3)*(3+1),350:((3+1)+4)*3,550:(5+3)*(3*1),650:((3-1)+6)*3,850:(7*3)+(3*1),950:((8+1)*3)-3,1150:(9-3)*(3+1),1450:(10-(3-1))*3,490:((3-1)+4)*4,770:(5*4)+(3+1),910:6/(1-(3/4)),1190:(7*4)-(3+1),1330:((3+1)*4)+8,1610:(9-(3*1))*4,2030:(10-4)*(3+1),1430:(6*3)+(5+1),1870:(7+5)*(3-1),2090:(5-(3-1))*8,2530:((3*1)*5)+9,3190:(10*3)-(5+1),1690:(6+6)*(3-1),2210:(7-(3*1))*6,2470:(8-(3+1))*6,2990:((3-1)*9)+6,3770:((3*1)*10)-6,2890:(7-3)*(7-1),3230:(7-(3+1))*8,3910:(9/3)*(7+1),4930:((3-1)*7)+10,3610:((3+1)*8)-8,4370:(9*8)/(3*1),5510:(8/3)*(10-1),5290:(9/3)*(9-1),6670:((10+1)*3)-9,8410:(10+10)+(3+1),686:((4+1)*4)+4,1078:(5*4)+(4*1),1274:((6+1)*4)-4,1666:(7*4)-(4*1),1862:((4*1)*4)+8,2254:(9-(4-1))*4,2842:(10-4)*(4*1),1694:(5*4)+(5-1),2002:6/((5/4)-1),2618:(7*4)-(5-1),2926:(8-4)*(5+1),3542:((4-1)*5)+9,4466:(10-4)*(5-1),2366:((4+1)*6)-6,3094:(7-(4-1))*6,3458:(6-(4-1))*8,4186:(9-(4+1))*6,5278:((4-1)*10)-6,4046:(7-4)*(7+1),4522:(7-(4*1))*8,5474:(7-4)*(9-1),5054:(8-(4+1))*8,6118:(9*8)/(4-1),9338:(10+9)+(4+1),11774:(10+10)+(4*1),2662:(5-(1/5))*5,3146:(6*5)-(5+1),5566:(9-5)*(5+1),7018:((10-5)*5)-1,3718:((5*1)*6)-6,4862:(6*5)-(7-1),5434:(8-(5-1))*6,6578:(9-(5*1))*6,8294:(10-6)*(5+1),7106:(7-(5-1))*8,8602:(9-5)*(7-1),10846:(7*5)-(10+1),7942:((5-1)*8)-8,9614:(9-(5+1))*8,12122:(10+8)+(5+1),11638:(9+9)+(5+1),14674:(10+9)+(5*1),18502:(10+10)+(5-1),4394:((6-1)*6)-6,6422:6/(1-(6/8)),7774:(9-(6-1))*6,9802:(10-6)*(6*1),10166:(9-6)*(7+1),12818:(10+7)+(6+1),9386:(8-(6-1))*8,11362:(9+8)+(6+1),14326:(10-(6+1))*8,13754:(9+9)+(6*1),17342:(10+9)+(6-1),13294:(9+7)+(7+1),16762:(10-7)*(7+1),12274:(8+8)+(7+1),14858:(9-(7-1))*8,18734:(10+8)+(7-1),17986:(9+9)+(7-1),22678:(10-7)*(9-1),13718:(8+8)+(8*1),16606:(9+8)+(8-1),20938:(10-(8-1))*8,135:(3*2)*(2+2),189:(4+2)*(2+2),297:((5*2)+2)*2,459:((7*2)-2)*2,513:(8-2)*(2+2),621:((9+2)*2)+2,783:(10*2)+(2+2),225:(3+3)*(2+2),315:((2+2)+4)*3,495:((5*2)-2)*3,585:((2/2)+3)*6,765:((2/2)+7)*3,855:(8*3)+(2-2),1035:(9-3)*(2+2),1305:((10+3)*2)-2,441:((4*2)-2)*4,693:(5*4)+(2+2),819:(6*4)+(2-2),1071:(7*4)-(2+2),1197:((2+2)*4)+8,1449:(9*2)+(4+2),1827:(10-4)*(2+2),1089:(5*5)-(2/2),1287:(5-(2/2))*6,1683:(7*2)+(5*2),1881:((8+5)*2)-2,2277:((5-2)+9)*2,2871:((5+2)*2)+10,1521:(6/2)*(6+2),1989:((7+2)*2)+6,2223:(8-(2+2))*6,2691:((6/2)+9)*2,3393:(10*2)+(6-2),2601:((7-2)+7)*2,2907:(7-(2+2))*8,4437:((10/2)+7)*2,3249:((2+2)*8)-8,3933:(9*2)+(8-2),4959:(10-2)+(8*2),6003:((9-2)*2)+10,7569:(10+10)+(2+2),375:((3+2)+3)*3,825:((5+2)*3)+3,975:((3-2)+3)*6,1275:((3-2)+7)*3,1425:(8*3)*(3-2),1725:((3+2)*3)+9,2175:(10*3)-(3*2),735:((3+2)*4)+4,1155:((3-2)+5)*4,1365:(6*4)*(3-2),1785:(7-(3-2))*4,1995:(4-(3-2))*8,2415:(9*4)/(3/2),3045:(10*3)-(4+2),1815:(5*5)-(3-2),2145:(5-(3-2))*6,2805:(7*3)+(5-2),3135:(5+3)+(8*2),3795:(9-5)*(3*2),4785:(5-3)*(10+2),2535:((3+2)*6)-6,3315:(7*3)+(6/2),3705:((8+2)*3)-6,4485:(9-(3+2))*6,5655:(10-6)*(3*2),4335:(7+3)+(7*2),4845:(8/3)*(7+2),5865:(9+7)*(3/2),7395:(7-3)+(10*2),5415:(8-(3+2))*8,6555:(9-(3*2))*8,8265:(10+8)+(3*2),7935:(9+9)+(3*2),10005:(10+9)+(3+2),12615:((10-3)*2)+10,1029:((4-2)+4)*4,1617:((5+2)*4)-4,1911:((4*2)-4)*6,2499:(7-4)*(4*2),2793:(8-4)*(4+2),3381:((9-2)*4)-4,4263:((4-2)*10)+4,2541:((5+5)*2)+4,3003:(6*5)-(4+2),3927:(7+5)*(4-2),4389:(5-(4-2))*8,5313:(9-5)*(4+2),6699:(10+4)+(5*2),3549:(6+6)*(4-2),4641:(7-4)*(6+2),5187:(8*6)/(4-2),6279:((4-2)*9)+6,7917:(10-6)*(4+2),6069:((7+7)*2)-4,6783:((7*2)-8)*4,8211:(9+7)+(4*2),10353:((4-2)*7)+10,7581:((4-2)*8)+8,9177:(9-(4+2))*8,11571:(10+8)+(4+2),11109:(9+9)+(4+2),14007:(10-4)+(9*2),17661:((4/10)+2)*10,6171:(5+5)+(7*2),6897:((5/5)+2)*8,8349:((5-2)*5)+9,10527:(5-(2/10))*5,5577:((5-2)*6)+6,7293:(7-(5-2))*6,8151:(6-(5-2))*8,9867:((5/2)*6)+9,12441:((5-2)*10)-6,9537:(7+7)+(5*2),10659:((5*2)-7)*8,12903:(7*5)-(9+2),16269:(10+7)+(5+2),11913:(8*5)-(8*2),14421:(9+8)+(5+2),18183:(10-(5+2))*8,22011:(9-5)+(10*2),27753:(10/5)*(10+2),6591:(6+6)+(6*2),8619:(7-(6/2))*6,9633:(8-(6-2))*6,11661:(9-6)*(6+2),14703:(10+6)+(6+2),12597:(7-(6-2))*8,15249:(9+7)+(6+2),19227:(10-7)*(6+2),14079:(8+8)+(6+2),17043:((6*2)-9)*8,21489:(10-8)*(6*2),20631:((9-6)+9)*2,26013:(9-6)*(10-2),32799:(10+10)+(6-2),16473:(8+7)+(7+2),25143:((10/7)+2)*7,18411:(8-(7-2))*8,22287:((9+7)*2)-8,34017:(10+9)+(7-2),42891:(10-7)*(10-2),20577:((8/2)*8)-8,24909:(9-(8-2))*8,31407:(10+8)+(8-2),30153:(9+9)+(8-2),38019:(10-(9-2))*8,47937:(10+10)+(8/2),58029:(10+9)+(10/2),625:((3*3)*3)-3,875:((3*3)-3)*4,1375:(5*3)+(3*3),1625:(6*3)+(3+3),2125:(7-3)*(3+3),2375:((3+3)-3)*8,2875:(9-(3/3))*3,3625:(10*3)-(3+3),1225:(4*3)+(4*3),1925:((3/3)+5)*4,2275:(6*4)+(3-3),2975:(7-(3/3))*4,3325:(8-4)*(3+3),4025:(9-(4-3))*3,3025:(5*5)-(3/3),3575:(6*5)-(3+3),4675:((5*3)-7)*3,6325:(9-5)*(3+3),7975:(10+5)+(3*3),4225:((6/3)+6)*3,5525:(7*3)+(6-3),6175:((3*3)-6)*8,7475:(9+6)+(3*3),9425:(10-6)*(3+3),7225:((3/7)+3)*7,8075:(8+7)+(3*3),9775:(9-3)*(7-3),9025:8/(3-(8/3)),10925:(9-(3+3))*8,13775:(10+8)+(3+3),13225:(9+9)+(3+3),16675:(10*3)-(9-3),1715:((4+3)*4)-4,2695:((4-3)+5)*4,3185:(6*4)*(4-3),4165:(7-(4-3))*4,4655:((4+3)-4)*8,5635:(9*4)-(4*3),7105:((10-3)*4)-4,4235:(5*5)-(4-3),5005:(5-(4-3))*6,6545:(7+5)+(4*3),7315:(8*4)-(5+3),8855:((5*3)-9)*4,11165:(10/5)*(4*3),5915:(6+6)+(4*3),8645:(8-6)*(4*3),10465:(9-(6-3))*4,13195:(10-4)+(6*3),10115:(7*4)-(7-3),11305:((7-3)*4)+8,13685:(9-7)*(4*3),17255:(10+7)+(4+3),15295:(9+8)+(4+3),19285:(10-(4+3))*8,18515:(9+9)*(4/3),29435:(10*3)-(10-4),7865:((5+5)*3)-6,10285:(7+5)*(5-3),11495:(8-5)*(5+3),13915:(9-(5/5))*3,9295:(6+6)*(5-3),12155:(7+5)*(6/3),13585:(8*6)/(5-3),16445:(9-6)*(5+3),20735:(10+6)+(5+3),17765:(8-5)+(7*3),21505:(9+7)+(5+3),27115:(10-7)*(5+3),19855:(8+8)+(5+3),24035:(9*3)-(8-5),29095:((5/3)*9)+9,36685:(10/5)*(9+3),46255:(10-(10/5))*3,10985:((6-3)*6)+6,14365:(7-(6-3))*6,16055:((6+3)-6)*8,19435:(9+6)+(6+3),24505:((6-3)*10)-6,18785:((7-6)+7)*3,20995:(8+7)+(6+3),25415:(9-6)+(7*3),32045:((6/3)*7)+10,23465:((6/3)*8)+8,28405:(9*8)/(6-3),35815:(10-(8-6))*3,34385:(9*3)-(9-6),43355:(10-6)*(9-3),54665:(3-(6/10))*10,24565:(7+7)+(7+3),27455:((7+3)-7)*8,33235:(9-(7/7))*3,41905:(10-7)+(7*3),30685:((7-3)*8)-8,37145:(9-(8-7))*3,44965:(9-7)*(9+3),56695:(9*3)-(10-7),71485:(10+10)+(7-3),34295:((8+3)-8)*8,41515:(9-8)*(8*3),52345:((10*8)-8)/3,50255:(9-9)+(8*3),63365:(10+9)+(8-3),79895:(10-10)+(8*3),60835:(9+9)+(9-3),76705:((9+9)-10)*3,96715:(9-(10/10))*3,2401:(4*4)+(4+4),3773:((4/4)+5)*4,4459:((4+4)-4)*6,5831:(7-4)*(4+4),6517:(8*4)-(4+4),7889:((9-4)*4)+4,9947:(10*4)-(4*4),5929:(5*5)-(4/4),7007:(5-(4/4))*6,9163:(7-(5-4))*4,10241:(8-5)*(4+4),15631:((10-5)*4)+4,12103:(8+4)*(6-4),14651:(9-6)*(4+4),18473:(10+6)+(4+4),14161:(4-(4/7))*7,15827:(7*4)-(8-4),19159:(9+7)+(4+4),24157:(10-7)*(4+4),17689:(8+8)+(4+4),21413:(9*4)-(8+4),26999:(10-4)*(8-4),41209:((10*10)-4)/4,9317:(5*5)-(5-4),11011:((5+4)-5)*6,14399:(7-(5/5))*4,16093:(4-(5/5))*8,19481:(9-5)+(5*4),24563:(10+5)+(5+4),13013:(6-5)*(6*4),17017:(7+5)*(6-4),19019:((5+4)-6)*8,23023:(9+6)+(5+4),29029:(10-6)+(5*4),22253:(7*5)-(7+4),24871:(8+7)+(5+4),30107:((7-4)*5)+9,37961:((7-5)*10)+4,27797:(5-(8/4))*8,33649:(9-(8-5))*4,42427:(10/5)*(8+4),40733:((9/9)+5)*4,51359:(9-5)*(10-4),64757:((10/5)*10)+4,15379:((6+4)-6)*6,20111:(7-6)*(6*4),22477:(8+6)+(6+4),27209:((6-4)*9)+6,34307:(10+6)*(6/4),26299:(7+7)+(6+4),29393:((6+4)-7)*8,35581:(9+7)*(6/4),44863:((6-4)*7)+10,32851:((6-4)*8)+8,39767:(9-8)*(6*4),50141:((8-6)*10)+4,48139:(9-9)+(6*4),60697:(10-9)*(6*4),76531:(10-10)+(6*4),34391:(7-(7/7))*4,38437:((7+7)-8)*4,42959:((7+4)-8)*8,52003:(9*8)/(7-4),65569:((7/4)*8)+10,62951:(7-(9/9))*4,79373:(10*4)-(9+7),100079:(7-(10/10))*4,48013:((8-4)*8)-8,58121:((8+4)-9)*8,73283:(10-8)*(8+4),70357:(4-(9/9))*8,88711:((9+4)-10)*8,111853:(10+10)+(8-4),107387:(10+9)+(9-4),14641:(5*5)-(5/5),17303:(5*5)-(6-5),30613:(9+5)+(5+5),20449:((5+5)-6)*6,26741:(5*5)-(7-6),29887:(8+6)+(5+5),34969:(7+7)+(5+5),39083:((5+5)-7)*8,59653:(10/5)*(7+5),43681:(5*5)-(8/8),52877:(5*5)-(9-8),66671:(10+5)*(8/5),64009:(5*5)-(9/9),80707:(5*5)-(10-9),101761:(5*5)-(10/10),24167:(5-(6/6))*6,31603:(7+6)+(6+5),35321:((8-5)*6)+6,42757:(9*6)-(6*5),53911:((10-5)*6)-6,41327:(5-(7/7))*6,46189:(8-6)*(7+5),55913:((7-5)*9)+6,51623:((6+5)-8)*8,62491:((8+5)-9)*6,78793:(6*5)/(10/8),75647:((9-6)*5)+9,95381:((9+5)-10)*6,120263:(10+10)*(6/5),73117:(9-7)*(7+5),92191:((7-5)*7)+10,67507:((7-5)*8)+8,81719:((7+5)-9)*8,103037:(10-8)*(7+5),124729:((10-7)*5)+9,157267:((7/5)*10)+10,75449:(8*5)-(8+8),91333:(9*8)/(8-5),115159:((8+5)-10)*8,212773:(10+10)+(9-5),28561:(6+6)+(6+6),41743:(8-6)*(6+6),50531:(6*6)/(9/6),63713:(10*6)-(6*6),66079:(9-7)*(6+6),83317:((10-7)*6)+6,61009:(8*6)/(8-6),73853:((6+6)-9)*8,93119:(10-8)*(6+6),112723:((9-6)*10)-6,108953:((7+7)-10)*6,96577:(8*6)/(9-7),121771:((7+6)-10)*8,116909:(7*6)-(9+9),185861:((10-7)*10)-6,89167:((8-6)*8)+8,107939:(9*8)-(8*6),136097:(8*6)/(10-8),130663:(9+9)*(8/6),164749:((10-8)*9)+6,199433:((9/6)*10)+9,317057:(10+10)+(10-6),192763:((9-7)*7)+10,141151:((9-7)*8)+8,177973:(10*8)-(8*7),215441:(9*8)/(10-7),271643:((10-8)*7)+10,198911:((10-8)*8)+8', ',') AS kv
)
SELECT c.id, c1, c2, c3, c4, result
FROM poker24.cards c LEFT JOIN a a ON a.i =
( CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);

The uncompressed SQL is 10,896 characters. Converting repeated logic into an inline function or using hexadecimal keys can reduce it, but the contest rules disallow stored procedures, so the large lookup string is the main compression target.

Compressed Variant

Pigsty provides the pgsql-gzip extension used by this historical solution:

CREATE EXTENSION IF NOT EXISTS gzip;

Compressing the lookup table reduces its 10,018 characters to 7,796 and the complete statement to 8,796 characters, within the contest limit:

WITH a(i, result) AS (SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
FROM regexp_split_to_table(encode(gunzip('\x1F8B08000000000000034D5A5B722B2B0CDC4EE278CABC24E0EE7F6157DD2DF0A9CA47860109F468B518576BFFFDFCD4BFFA1B7FAFF5AEE6FFFDF8ABFDBE38F86E65FCF733F1EEA7F1B92DCC7FC5FC86F96DC6FCFDDCF77DC4FB5AFEAE803ACA7F3FE3D5AFC016CF46819DCF5ECE06FCF7D54340390A269F573CAF58FFF75343CD7B60FEFEBBF20CEF6B79F8840575FB11387E5FE3DDCBDDB1F1D9074E38AE409C603E9C81F7D6C3220B6BA5C0C738271C98BFEAB1D8AB96D0F11E2B26D8CB7E25E36D3326D811E8EF09934C2897C0D55DEFB9E1F5766CC0717ABDDF6BE1C4FEFB490B7E4FF4DA61A538E1BC5B98E1B71246C62635B27EFFC28DCD61F576DC5277C86CF48AD1EA1D46F8BBEF7BF1F37E3E742334B4E7B87954C8C7D4BFFDFB6A6F6B8D56FF2AB07043A742B9B596B3A0D3EA9D6EEF57E12E474187910CF327DDCCF736D3ED2F4E7A3BE6EF787EF47ECD747B7BC9ED6DC70E07DDC609C3EF09E8761B2EB7A7C0891385DBF180F713161AE779BDB733B0290CEF6BAB8823A84BBF4FD8587EA7C4658B7E958470538591E4782C0B113634E3251DD524136EB3B06CB809BBAA25ECF81713B1A65CCB4744C0F9BD295EB5B118182BD4F81908A9738FB353C64B6BB24586EC136B26FC1FF69EBFA1E4D3427167D041EFCC00C1F935808DB46DF7FC0ABA5661228D30E8424DC39A15912B2733AC7E1692A7690DC38461C030E978E6BFF05C7F9BDD30E93099EBFD73D061D90D13BEDF7CBF70B2088D487EC6E17EAE823A2C03A53F0A94B1AF48E2C3442419F1802B7644A247EAC58ACFF865FA51E7083F4B246399FF635518DC2B95724B10D94A97D2F1DD06469C1B67025606B082A3D3BE056AECEC33AC6952F33AC1D1B991080E248184302B041D12C2B49B6727E1FAC13CD2CE39B0EFF1151C9DE7971A05475B3C306D2830683DA56684F5CD037F3883C9B6FB95AAE0E8B4898CB47E9F40903ECB090EBACE98F28B42C2541869FB8ADD4C7AE7DEA29CC8BFFBBD46A50DFE908232AD2FC4D8486F44A296B98E4387D26E22D85D339E98E1085C795433161304FFCBA38D991A1E1D890E6D8D763DAA35BEC75163726069089C1F8BB0E18023BBA54633365277518629FA09B35022178F819DA51AADE97E8FE7F04E2F5BC0351266FA4062FA1900562F41D7489F5B8345A4462E1E651044C67520017DCA1E1062638E3383BEB00293AC2335CA56C5F1E45016C6DDBB6AFF86E555B9E61C5F77D16ECD3DCBE3C7428E45580B99ED44B599A0D78E9966214C86590C767A6A042D47EC75AC32E8410961CCDAE8DAAEA599DC6084B08A656A2C568C10EA574F2D82564B4B2E2FEDEC640A8D170DA7625FB868D38758A240DF5E153B76F0B85555CBBF75B3BF3A6CB5692A8D0C4F5371485169A57DADC770189DD8EECF39B88FD612AEFCB302AE264D1EEA3D0FBE97A4F09CFE524D9185FDB8BFB655E5BBC85E664A78733158534E1CA376D878F3149EA0D614AE7CE6A43E99393C8594CDAED4D110ADD869FA4D65D21F1C489B9CDF2D316D17D56964B0C26E7998DA16EB585A561E8A3AEE67032A5CCDE7BAB2B736C0F891ECA56CF6E2E7702BF158E1FCF05987B3C371409542FD06E5B8CF6D4F066523696A91549B45B6FD3E7CB6DA61D13BDF5B8DF79B9363C97BAE7E8BBF04363BD592CFBD1AEB78CB6A39B6A542088DEAB9F8FED392544DBFCF53D5D30E976E0F0E507022554B9DA81713E0F65F4A0D44AA4446A918C1C3FA413DAE58751F369D22673DA02791955621B754B51C631F663164C6362FE8694D8165935A7D1AE3738A39C513498FC356533CE94521AB9209586E3CC287DE884D89B28608CCB0343758B3C101FE81439C7AF7A2C7720A9853A3CBB82D964FDF10823592DAFBFE3ACD6181686830653EB27A28DE6526636B02E8A885B4F2E74CE90D369191082221B51F232162E0EA9D8CFB8F3CEDEDA57484CF73CF33CDF7172F1431D3588515111101CD8E0D220AFA7BEBFD7322266AE51D60C8D4D1EC10F14E07CF764442C40E1A8825494B901DEFD9EF0C55E46A5728B97820891D329E4211B960184F13DBDE08ED7106A2220FC4FE8E25411F1012BD8CAFDA8C234C51D4506AAB98624708980DC25BF41181110985AD826FA651FBDC76109F6719DC993D62294C4AFB1E4F15996929A9CEAD4D66D19289509DC63211C40C2373B38BC9D2D32175722793030B9B5FC9B11A1A5D180DA0F9920566DF269EF6A7008CA2879DACA3276AB4592A7E696035B70B9872D62606102F39504B297601BBD3B24165840B4FBFC953DA26A968C9A38305CF135BA259BB5EEC1822A37B1F4E81D1779BBB1F4244170683A827A6296334EFA925BBAE60664A6326FA1FFA7BE4814ABF846CE089A8F560EE74C2C9C327929B0E249697B9C47D2B73C6C193A066FB506B0971E8D5E60916D1BBCDD3A77386C771CE5E49ADE7AEF37A597A8A0B6026512AE09498AF1AB160C5D5603495C6F14A8CBE269899E7B41247D878859E998CAF65AD36805DFA59D9516BD9C7D11A19A51CE0FD23D6441EBA53F407C6A6CD83E74114EC9D91E94B752EF89B2E0756277A21A3B28D2DD60E5E8720B03CB30BC7EA636783EF49B6941391BD953CD6D24B51C8A5474B426C5335B28C86C8AC6D9DBE9EB70E1467D565519CA25FBBF4C3D9360FEE2D8192CBB24AB13873129120CA54AB871158E28B0AF4C367A2522B74D7633707A3EC18677DEC42466CA92A98FE78B716C4B2321388172469DE7BB2AD2C70959E104753718A568E82254679695BA5C5D36651D2585D93C7B1A6B52CAFF32BA92052573239FABD0C041936F76C9E2CD8960ACE226DC4C98A7765A79F921AA5AE9F4DB23645259BFB9F22C48A587D4C9C2EF91E31B4525F58693288665877C094EB61E5947159F505798D5571146554B23BA46574ABF51E4F7B6845C1B63EA79C865118FC0F8B295B5818E166084B6C2F159E53866864952A23109258BB03B2E6F77C50812BC8B6EFB658D6030C582450377931B1E6797EBA4AE064B1D24D466750DAB92100E50B0F343B6DB8064E31978C054693E81E558277A594714A31D65452C841A9836AB636162B548B1B2BBE185C7F3A596CD6624AC5D51D29405E66C48C519A65779C7A3990953756057A4AA89D7D447723AADA9995FDEDBD7D0B2D66CC2D1A419CA14506F71E29D2F3F2C7AC7D0B2DB6EAF56B558745E6A045982194B147FBA7D0524F4B034C529EF95E056B149C5A33E765C530FF7BE3782B78C7C37A0C90D9ED14F47EFA9EF94F61A5E93B356565E588FB3F54090A22F15858072F495110029938F01CFFF4BA2E57C268B4F76E790120FF0C9209CA808FA2BC794FAEF4C8E9D1D87ECB77D6D57E3D46A9C6A26F472AFAE561AAA21939933467E93A03C7596C27E4D3CD98AE55EC82D0C745017C76908F03CB496B5412199065B865C3AAF3D45EB7DDBAE49A54C5B106FB7B8C64AB327524F415DDC5B2E6151D54D52ECE0FBAC01A095ED64525C492363EABADB49A9E0B491FE6C4E85FCF7167D1ADB91D224296178C68D9211EA64DB2435B7995C1A0A041703BC0EB8F60E0DC9088861635D2658941F0A3EF5C76A886E6F818768097825B99DA214D2D5D93FDDF7A54B9092956EC54072D9B346CC2A7966DB58959F83069A84FE4E1210E2D8D5A4FD0D3CDCB49F7F5F5FD56CEA7F91F0DB39D289B3D2A7C9DF7D9A3673C7B465EF5C2C0F2BF93D655E6DF59F9B8259E4472F24E7B91AA8724CFDE255AF87D535BCBC49059C16492DED847D0D0E75EBB332235A48BED356837DE751179C2236937C6B23E5CF575AD040DE4F45FF461BEDB70C8EEA8FC6446D0378C16C8F248AF0C5A000F223181C16AD57F6600177BFFBACBF1DC394BF17573426DE4AC8A93D8652E1BDB6F96D04DE6849C7D427BF2DB889CA922C784EB83811AD6ECA4AAB867549A90212C667B984E4043F5BF9FC0ECD2D482B0A86252301DFFF617EB21F6AFCC7815554E2BEBDB98D076D3D557610813913C3E339D22C268CF8E68AD2879BCFF0D428F1B6E12E8CF48481DBA98C14B3526B6FAE5F65CE256E7C13A0ECCC59B818D29EC69F71EA40109B20350D7EEA50574BD27E9B5E98924AFFAA1BC434857DAA8071FA8A79A3896EE3AD53DB75AFAF922E9409E1AF179B9A1962D32ACCC7E0D459DA86CA10723261896F1A24520BA2828C0E8B245AE429BFD658B12347D5DB6A84921BB9F02B33812FDD3BE7738943D6A03E58289909FD1B787D13ACC2A13193750C99F03664294E96B565793980089BEB2A05318678470B02EEBC65D1453A85FF660DC76273775DA16E51324B7DEC65086DCE477524FA4694165FA411ACA09A813B92366485B14F6DB514C998D974B2B8175904CC2FB0A2A7DBE99DB753164B7979D732B4416430479EEE310551D7FB421FE4E60A5B583B9765EFD7CF6F9B81925621F3AA5F2149CDBF296E9EA0B7ECB16D5F3BCD19287FD19FA7EACD4DA00795E89B51899F2244C96DF8C464FF2CC651F4640A3DF126B69385E8D499940CCD8B8EA0A83ABC658ECEF293A3F1C4511AD6788E8DBF7F47970867BB452D90892469C8FF0515A0FCE70127A6D85F23EEBA21EF6FAC5198EC559362D90C81A8C6BE97E0E6751530EE853DF3E12FBACF1D6411561D2DE66EAED3FDA373AE75826D1F0943E3277E529530786E075CB54C41F0CC36918BC22DD44F2B05CD305E7C80E6D86A5FAEDD01818D120C2E7E3288CD63CE25297CC439889BB81E037FD9F1686991021B5BEADD54E988195335D238A70975FFA194166A1E4100A32EF8C3F18D16D403C648CF9FCCA41A44568AC75638CAB7A94A51B3E1AD985572394C3F0B1A85CDFCE1A691C15D6D715BD3E0B2568CD8B310899B707EBAE890D61279CC3472917AB612A3401E52E63C880734EAFDF31F806F8E8CA58FFB83EBF053E010C325FB073EB7215110DF912190CBF6CDC17B22B7A5BD7E598705E9FB0A261906805628C38BF30ACFC4E8365C66B0A610853D1A26F54965986A647B37BAEC211291EC58BF76C50FCC141C228D37CCCECE5054FDBF2EE0DCB1029B80C2ECD6FA420658D6D409D87407053BBD57D814D491C7D4EA29F6512AFE874944296F11B55ADA895660053540DF069AA1A90AFCB249B8D1741F30019AFC01865795F13A5298A6BEF372349522BF8C93E9650F047533DE73FC1BFC96697F9F77EE60FCCADCED18FE539127413D0E1E4E03B7C1F3C6656E5B2BC8A21672AEFBC6A71FCD687252FCFC360F0CAE0139B8786302913922B649B2894F59FDB1748AAB1F3D68FCB92F296E04DFD40959C164932EFBD2476020231F9E903317A41C0792332B979302A743DCBEBDDAB34ACCD789725F4CBA238229116B84435E8BCCABE3AB969945FF77E9AA80583E11A68A473D7FD2953507BD5B28472FCCE2178DE3F772C2CBDE8D3A6EBFCF3FBB3A7CA4B438D697B28A9728BF637D9F6F0E250B1218A1B8D8FE715143693F282879EB45C12F83F3248B10120270000'), 'escape'), ',') AS kv)
SELECT c.id, c1, c2, c3, c4, result FROM poker24.cards c LEFT JOIN a a ON a.i =
(CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);

Result

On a local M1 MacBook Pro, single-core execution took about 0.58 seconds, slightly faster than the winning 0.67-second result. The contest PostgreSQL instance did not provide the gzip extension, so this compressed form was not submitted there.

 Merge Right Join  (cost=118104.17..768224.17 rows=5000000 width=68) (actual time=457.485..555.265 rows=1000000 loops=1)
   Merge Cond: (((split_part(kv.kv, ':'::text, 1))::integer) = ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END)))
   ->  Sort  (cost=62.33..64.83 rows=1000 width=64) (actual time=0.851..0.872 rows=566 loops=1)
         Sort Key: ((split_part(kv.kv, ':'::text, 1))::integer)
         Sort Method: quicksort  Memory: 59kB
         ->  Function Scan on regexp_split_to_table kv  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.491..0.654 rows=566 loops=1)
   ->  Sort  (cost=118041.84..120541.84 rows=1000000 width=36) (actual time=456.629..494.693 rows=1000000 loops=1)
         Sort Key: ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END))
         Sort Method: external sort  Disk: 56760kB
         ->  Seq Scan on cards c  (cost=0.00..18384.00 rows=1000000 width=36) (actual time=0.028..213.760 rows=1000000 loops=1)
 Planning Time: 0.363 ms
 Execution Time: 581.782 ms

This demonstrates a one-statement PostgreSQL solution to the 24-point card game. Parallel execution could improve it further; a PostgreSQL-specific alternative would be a C extension exposing the lookup as a function, although that would fall outside the contest rules.

5 - StackOverflow Global Developer Survey

Analyze database-related data from StackOverflow’s global developer survey over the past seven years

Overview

GitHub Repository: https://github.com/Vonng/pigsty-app/tree/11fbbc03031b0a060cf4da0f87f3f12a4ec2f126/db

Online Demo: https://demo.pigsty.io/d/sf-survey

6 - DB-Engines Database Popularity Trend Analysis

Analyze database management systems on DB-Engines and browse their popularity evolution

Overview

GitHub Repository: https://github.com/Vonng/pigsty-app/tree/11fbbc03031b0a060cf4da0f87f3f12a4ec2f126/db

Online Demo: https://demo.pigsty.io/d/db-engine

7 - AWS & Aliyun Server Pricing

Analyze compute and storage pricing on Aliyun / AWS (ECS/ESSD)

Overview

Historical source note: the advertised pigsty-app/cloud directory was not present in the last immutable app repository snapshot before v2.7.0; the SQL and recovered images below come from the frozen documentation repository.

Online Demo: https://demo.pigsty.io/d/ecs

Article: Analyzing Computing Costs: Has Aliyun Really Reduced Prices?

Data Source

Aliyun ECS pricing can be obtained as raw CSV data from Price Calculator - Pricing Details - Price Download.

Schema

Download Aliyun pricing details and import for analysis

CREATE EXTENSION file_fdw;
CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;

DROP FOREIGN TABLE IF EXISTS aliyun_ecs CASCADE;
CREATE FOREIGN TABLE aliyun_ecs
    (
        "region" text,
        "system" text,
        "network" text,
        "isIO" bool,
        "instanceId" text,
        "hourlyPrice" numeric,
        "weeklyPrice" numeric,
        "standard" numeric,
        "monthlyPrice" numeric,
        "yearlyPrice" numeric,
        "2yearPrice" numeric,
        "3yearPrice" numeric,
        "4yearPrice" numeric,
        "5yearPrice" numeric,
        "id" text,
        "instanceLabel" text,
        "familyId" text,
        "serverType" text,
        "cpu" text,
        "localStorage" text,
        "NvmeSupport" text,
        "InstanceFamilyLevel" text,
        "EniTrunkSupported" text,
        "InstancePpsRx" text,
        "GPUSpec" text,
        "CpuTurboFrequency" text,
        "InstancePpsTx" text,
        "InstanceTypeId" text,
        "GPUAmount" text,
        "InstanceTypeFamily" text,
        "SecondaryEniQueueNumber" text,
        "EniQuantity" text,
        "EniPrivateIpAddressQuantity" text,
        "DiskQuantity" text,
        "EniIpv6AddressQuantity" text,
        "InstanceCategory" text,
        "CpuArchitecture" text,
        "EriQuantity" text,
        "MemorySize" numeric,
        "EniTotalQuantity" numeric,
        "PhysicalProcessorModel" text,
        "InstanceBandwidthRx" numeric,
        "CpuCoreCount" numeric,
        "Generation" text,
        "CpuSpeedFrequency" numeric,
        "PrimaryEniQueueNumber" text,
        "LocalStorageCategory" text,
        "InstanceBandwidthTx" text,
        "TotalEniQueueQuantity" text
        ) SERVER fs OPTIONS ( filename '/tmp/aliyun-ecs.csv', format 'csv',header 'true');

Similarly for AWS EC2, you can download the price list from Vantage:


DROP FOREIGN TABLE IF EXISTS aws_ec2 CASCADE;
CREATE FOREIGN TABLE aws_ec2
    (
        "name" TEXT,
        "id" TEXT,
        "Memory" TEXT,
        "vCPUs" TEXT,
        "GPUs" TEXT,
        "ClockSpeed" TEXT,
        "InstanceStorage" TEXT,
        "NetworkPerformance" TEXT,
        "ondemand" TEXT,
        "reserve" TEXT,
        "spot" TEXT
        ) SERVER fs OPTIONS ( filename '/tmp/aws-ec2.csv', format 'csv',header 'true');



DROP VIEW IF EXISTS ecs;
CREATE VIEW ecs AS
SELECT "region"                                       AS region,
       "id"                                           AS id,
       "instanceLabel"                                AS name,
       "familyId"                                     AS family,
       "CpuCoreCount"                                 AS cpu,
       "MemorySize"                                   AS mem,
       round("5yearPrice" / "CpuCoreCount" / 60, 2)   AS ycm5, -- ¥ / (core·month)
       round("4yearPrice" / "CpuCoreCount" / 48, 2)   AS ycm4, -- ¥ / (core·month)
       round("3yearPrice" / "CpuCoreCount" / 36, 2)   AS ycm3, -- ¥ / (core·month)
       round("2yearPrice" / "CpuCoreCount" / 24, 2)   AS ycm2, -- ¥ / (core·month)
       round("yearlyPrice" / "CpuCoreCount" / 12, 2)  AS ycm1, -- ¥ / (core·month)
       round("standard" / "CpuCoreCount", 2)          AS ycmm, -- ¥ / (core·month)
       round("hourlyPrice" / "CpuCoreCount" * 720, 2) AS ycmh, -- ¥ / (core·month)
       "CpuSpeedFrequency"::NUMERIC                   AS freq,
       "CpuTurboFrequency"::NUMERIC                   AS freq_turbo,
       "Generation"                                   AS generation
FROM aliyun_ecs
WHERE system = 'linux';

DROP VIEW IF EXISTS ec2;
CREATE VIEW ec2 AS
SELECT id,
       name,
       split_part(id, '.', 1)                                                               as family,
       split_part(id, '.', 2)                                                               as spec,
       (regexp_match(split_part(id, '.', 1), '^[a-zA-Z]+(\d)[a-z0-9]*'))[1]                 as gen,
       regexp_substr("vCPUs", '^[0-9]+')::int                                               as cpu,
       regexp_substr("Memory", '^[0-9]+')::int                                              as mem,
       CASE spot
           WHEN 'unavailable' THEN NULL
           ELSE round((regexp_substr("spot", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END     AS spot,
       CASE ondemand
           WHEN 'unavailable' THEN NULL
           ELSE round((regexp_substr("ondemand", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END AS ondemand,
       CASE reserve
           WHEN 'unavailable' THEN NULL
           ELSE round((regexp_substr("reserve", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END  AS reserve,
       "ClockSpeed"                                                                         AS freq
FROM aws_ec2;

Visualization