YSQL Functions

SELECT 
  COUNT(*) AS total_orders,
  AVG(total) AS avg_order_value,
  SUM(total) AS total_revenue,
  MIN(order_date) AS first_order,
  MAX(order_date) AS last_order
FROM orders;

SELECT 
  customer_id,
  string_agg(product_name, ', ' ORDER BY product_name) AS products,
  array_agg(order_id ORDER BY order_date) AS order_ids
FROM order_items
GROUP BY customer_id;

SELECT 
  stddev(price) AS standard_deviation,
  variance(price) AS variance,
  stddev_pop(price) AS population_stddev,
  var_pop(price) AS population_variance
FROM products;

SELECT 
  jsonb_agg(row_to_json(t)) AS json_array,
  jsonb_object_agg(name, value) AS json_object
FROM (
  SELECT name, value FROM metrics
) t;

SELECT 
  product_name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank,
  NTILE(4) OVER (ORDER BY price DESC) AS quartile
FROM products;

SELECT 
  order_id,
  order_date,
  total,
  LAG(total) OVER (ORDER BY order_date) AS prev_order_total,
  LEAD(total) OVER (ORDER BY order_date) AS next_order_total,
  FIRST_VALUE(total) OVER (ORDER BY order_date) AS first_order_total,
  LAST_VALUE(total) OVER (
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_total
FROM orders;

SELECT 
  order_date,
  total,
  SUM(total) OVER (
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(total) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM orders
ORDER BY order_date;

SELECT 
  -- Case conversion
  UPPER('hello') AS upper_case,           -- HELLO
  LOWER('WORLD') AS lower_case,           -- world
  INITCAP('hello world') AS title_case,   -- Hello World
  
  -- Concatenation
  'Hello' || ' ' || 'World' AS concat,    -- Hello World
  CONCAT('Hello', ' ', 'World') AS concat_fn,
  
  -- Substring
  SUBSTRING('Hello World' FROM 1 FOR 5) AS substr,  -- Hello
  LEFT('Hello World', 5) AS left_chars,    -- Hello
  RIGHT('Hello World', 5) AS right_chars,  -- World
  
  -- Padding
  LPAD('123', 6, '0') AS left_pad,        -- 000123
  RPAD('123', 6, '0') AS right_pad,       -- 123000
  
  -- Trimming
  TRIM('  hello  ') AS trimmed,           -- hello
  LTRIM('  hello  ') AS left_trim,        -- 'hello  '
  RTRIM('  hello  ') AS right_trim;       -- '  hello'

SELECT 
  -- Position
  POSITION('world' IN 'hello world') AS pos,  -- 7
  
  -- Replace
  REPLACE('hello world', 'world', 'universe') AS replaced,
  
  -- Regular expressions
  REGEXP_REPLACE('hello123world', '[0-9]+', 'X') AS regex_replace,
  REGEXP_MATCHES('test123demo456', '[0-9]+', 'g') AS regex_matches,
  
  -- Pattern matching
  'hello' LIKE 'h%' AS like_result,       -- true
  'hello' SIMILAR TO 'h.*' AS similar_to, -- true
  'hello' ~ '^h.*o$' AS regex_match;      -- true

SELECT 
  LENGTH('hello') AS str_length,          -- 5
  CHAR_LENGTH('hello') AS char_length,    -- 5
  BIT_LENGTH('hello') AS bit_length,      -- 40
  OCTET_LENGTH('hello') AS octet_length;  -- 5

SELECT 
  CURRENT_DATE AS current_date,
  CURRENT_TIME AS current_time,
  CURRENT_TIMESTAMP AS current_timestamp,
  NOW() AS now,
  CLOCK_TIMESTAMP() AS clock_time,
  TRANSACTION_TIMESTAMP() AS txn_time;

SELECT 
  EXTRACT(YEAR FROM TIMESTAMP '2024-03-15 14:30:00') AS year,
  EXTRACT(MONTH FROM TIMESTAMP '2024-03-15 14:30:00') AS month,
  EXTRACT(DAY FROM TIMESTAMP '2024-03-15 14:30:00') AS day,
  EXTRACT(HOUR FROM TIMESTAMP '2024-03-15 14:30:00') AS hour,
  EXTRACT(DOW FROM TIMESTAMP '2024-03-15 14:30:00') AS day_of_week,
  EXTRACT(EPOCH FROM TIMESTAMP '2024-03-15 14:30:00') AS unix_time;

SELECT 
  -- Addition
  CURRENT_DATE + INTERVAL '7 days' AS next_week,
  CURRENT_DATE + INTERVAL '1 month' AS next_month,
  
  -- Subtraction
  CURRENT_DATE - INTERVAL '1 year' AS last_year,
  
  -- Age calculation
  AGE(TIMESTAMP '2024-03-15', TIMESTAMP '2020-01-01') AS age,
  
  -- Date difference
  DATE '2024-12-31' - DATE '2024-01-01' AS days_diff;

SELECT 
  TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS formatted_timestamp,
  TO_CHAR(CURRENT_DATE, 'Mon DD, YYYY') AS formatted_date,
  TO_DATE('2024-03-15', 'YYYY-MM-DD') AS parsed_date,
  TO_TIMESTAMP('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS parsed_timestamp;

SELECT 
  DATE_TRUNC('year', CURRENT_TIMESTAMP) AS year_start,
  DATE_TRUNC('month', CURRENT_TIMESTAMP) AS month_start,
  DATE_TRUNC('day', CURRENT_TIMESTAMP) AS day_start,
  DATE_TRUNC('hour', CURRENT_TIMESTAMP) AS hour_start;

SELECT 
  ABS(-42) AS absolute,              -- 42
  CEIL(4.3) AS ceiling,              -- 5
  FLOOR(4.8) AS floor,               -- 4
  ROUND(4.567, 2) AS rounded,        -- 4.57
  TRUNC(4.567, 2) AS truncated,      -- 4.56
  MOD(10, 3) AS modulo,              -- 1
  POWER(2, 10) AS power,             -- 1024
  SQRT(16) AS square_root,           -- 4
  CBRT(27) AS cube_root;             -- 3

SELECT 
  SIN(PI() / 2) AS sine,
  COS(0) AS cosine,
  TAN(PI() / 4) AS tangent,
  ASIN(1) AS arcsine,
  ACOS(0) AS arccosine,
  ATAN(1) AS arctangent;

SELECT 
  RANDOM() AS random_0_to_1,
  FLOOR(RANDOM() * 100)::INT AS random_0_to_99,
  LN(2.718281828) AS natural_log,
  LOG(10, 100) AS log_base_10,
  EXP(1) AS exponential;

SELECT 
  product_name,
  price,
  CASE 
    WHEN price < 10 THEN 'Budget'
    WHEN price < 50 THEN 'Standard'
    WHEN price < 100 THEN 'Premium'
    ELSE 'Luxury'
  END AS price_category
FROM products;

SELECT 
  COALESCE(email, phone, 'No contact') AS contact_info,
  NULLIF(status, '') AS status_or_null,
  GREATEST(10, 20, 30) AS maximum,
  LEAST(10, 20, 30) AS minimum
FROM contacts;

SELECT 
  data->>'name' AS name,                    -- Text extraction
  data->'address'->>'city' AS city,         -- Nested extraction
  data->'tags'->0 AS first_tag,             -- Array element
  jsonb_array_length(data->'tags') AS tag_count
FROM users;

SELECT 
  jsonb_build_object(
    'id', user_id,
    'name', username,
    'email', email
  ) AS user_json,
  jsonb_build_array(1, 2, 3, 4, 5) AS number_array
FROM users;

SELECT * FROM users
WHERE 
  data @> '{"status": "active"}'::jsonb          -- Contains
  AND data ? 'email'                               -- Key exists
  AND data->'age' > '25'::jsonb;                   -- Comparison

SELECT 
  -- Array construction
  ARRAY[1, 2, 3, 4, 5] AS int_array,
  
  -- Array operations
  array_length(ARRAY[1, 2, 3], 1) AS arr_length,
  array_append(ARRAY[1, 2, 3], 4) AS appended,
  array_prepend(0, ARRAY[1, 2, 3]) AS prepended,
  array_cat(ARRAY[1, 2], ARRAY[3, 4]) AS concatenated,
  
  -- Array search
  1 = ANY(ARRAY[1, 2, 3]) AS contains_one,
  ARRAY[1, 2] <@ ARRAY[1, 2, 3, 4] AS is_subset,
  
  -- Array to string
  array_to_string(ARRAY['a', 'b', 'c'], ',') AS joined;

SELECT 
  version() AS pg_version,
  current_database() AS db_name,
  current_schema() AS schema_name,
  current_user AS current_user,
  session_user AS session_user,
  inet_client_addr() AS client_ip,
  inet_server_addr() AS server_ip,
  pg_backend_pid() AS backend_pid;

-- Get tablet information
SELECT yb_hash_code(user_id) AS hash_code FROM users;

-- Get server UUID
SELECT yb_server_uuid();

-- Get cluster UUID  
SELECT yb_cluster_uuid();

-- Get connection statistics (requires yb_pg_metrics extension)
CREATE EXTENSION IF NOT EXISTS yb_pg_metrics;

SELECT * FROM yb_pg_stat_get_queries();

SELECT 
  -- Casting
  '123'::INTEGER AS to_int,
  123::TEXT AS to_text,
  CAST('2024-03-15' AS DATE) AS to_date,
  
  -- Type checking
  pg_typeof(123) AS type_of_int,
  pg_typeof('text') AS type_of_text,
  pg_typeof(ARRAY[1,2,3]) AS type_of_array;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT 
  MD5('password') AS md5_hash,
  encode(digest('password', 'sha256'), 'hex') AS sha256_hash,
  crypt('password', gen_salt('bf')) AS bcrypt_hash,
  gen_random_uuid() AS random_uuid;

SELECT 
  to_tsvector('english', 'The quick brown fox') AS vector,
  to_tsquery('english', 'fox & quick') AS query,
  to_tsvector('english', 'The quick brown fox') @@ 
    to_tsquery('english', 'fox & quick') AS matches;