Advanced Functions

Leverage specialized functions for mathematical operations, geospatial queries, and vector similarity search.

Mathematical Functions

Function	Description	Example
ABS(x)	Absolute value	SELECT ABS(temperature) FROM sensors
ROUND(x, d)	Round to d decimal places	SELECT ROUND(avg_temp, 2) FROM sensors
CEIL(x) / CEILING(x)	Ceiling function	SELECT CEIL(response_time) FROM logs
FLOOR(x)	Floor function	SELECT FLOOR(response_time) FROM logs
SQRT(x)	Square root	SELECT SQRT(variance) FROM metrics
POWER(x, y) / POW(x, y)	x raised to power y	SELECT POWER(value, 2) FROM data
MOD(x, y)	Modulo operation	SELECT MOD(id, 10) FROM logs

Examples

-- Basic mathematical operations
SELECT 
  temperature,
  ABS(temperature) as abs_temp,
  ROUND(temperature, 1) as rounded_temp,
  CEIL(temperature) as ceiling_temp,
  FLOOR(temperature) as floor_temp
FROM sensor_readings;

-- Advanced calculations
SELECT 
  value,
  SQRT(value) as square_root,
  POWER(value, 2) as squared,
  MOD(id, 100) as bucket
FROM measurements
WHERE value > 0;

-- Statistical calculations
SELECT 
  sensor_id,
  COUNT(*) as reading_count,
  AVG(temperature) as avg_temp,
  ROUND(SQRT(SUM(POWER(temperature - avg_temp, 2)) / COUNT(*)), 2) as std_dev
FROM sensor_readings
GROUP BY sensor_id;

Geospatial Functions

Work with geographical data using spatial functions:

-- Calculate distance between two points
SELECT 
  location_name,
  ST_DISTANCE(coordinates, ST_POINT(37.7749, -122.4194)) as distance_from_sf
FROM locations
ORDER BY distance_from_sf;

-- Create points from latitude/longitude columns
SELECT 
  id,
  name,
  ST_POINT(latitude, longitude) as coordinates
FROM poi_data
WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

-- Find nearby locations
SELECT 
  l1.name as location1,
  l2.name as location2,
  ST_DISTANCE(l1.coordinates, l2.coordinates) as distance
FROM locations l1
CROSS JOIN locations l2
WHERE l1.id != l2.id
  AND ST_DISTANCE(l1.coordinates, l2.coordinates) < 1000  -- Within 1km
ORDER BY distance;

Geospatial Examples

-- Store locations analysis
SELECT 
  store_id,
  store_name,
  ST_DISTANCE(
    ST_POINT(store_lat, store_lng), 
    ST_POINT(37.7749, -122.4194)
  ) as distance_from_sf
FROM stores
WHERE ST_DISTANCE(
  ST_POINT(store_lat, store_lng), 
  ST_POINT(37.7749, -122.4194)
) < 50000  -- Within 50km
ORDER BY distance_from_sf;

-- Delivery route optimization
SELECT 
  d.delivery_id,
  d.customer_address,
  ST_DISTANCE(
    ST_POINT(d.customer_lat, d.customer_lng),
    ST_POINT(w.warehouse_lat, w.warehouse_lng)
  ) as delivery_distance
FROM deliveries d
CROSS JOIN warehouses w
WHERE d.status = 'pending'
ORDER BY delivery_distance;

Vector Functions

Perform similarity searches using vector embeddings:

-- K-nearest neighbors search
SELECT 
  id,
  title,
  content,
  KNN_SEARCH(VECTOR[0.1, 0.2, 0.3, 0.4, 0.5], 'embeddings', 10) as similarity_score
FROM documents
WHERE KNN_SEARCH(VECTOR[0.1, 0.2, 0.3, 0.4, 0.5], 'embeddings', 10) > 0.7
ORDER BY similarity_score DESC;

Vector Search Examples

-- Document similarity search
SELECT 
  d.id,
  d.title,
  d.category,
  KNN_SEARCH(
    VECTOR[0.15, 0.32, 0.18, 0.45, 0.67, 0.23, 0.89, 0.12], 
    'content_embeddings', 
    5
  ) as relevance_score
FROM documents d
WHERE d.published = true
  AND KNN_SEARCH(
    VECTOR[0.15, 0.32, 0.18, 0.45, 0.67, 0.23, 0.89, 0.12], 
    'content_embeddings', 
    5
  ) > 0.8
ORDER BY relevance_score DESC
LIMIT 10;

-- Product recommendation based on vector similarity
SELECT 
  p.product_id,
  p.name,
  p.category,
  p.price,
  KNN_SEARCH(
    query_embedding.vector, 
    'product_features', 
    20
  ) as similarity
FROM products p
CROSS JOIN (
  SELECT VECTOR[0.25, 0.41, 0.33, 0.67, 0.12] as vector
) as query_embedding
WHERE p.in_stock = true
  AND KNN_SEARCH(query_embedding.vector, 'product_features', 20) > 0.6
ORDER BY similarity DESC
LIMIT 10;

Common Parameters

When working with advanced functions, you can control various aspects of your queries:

Parameter	Type	Default	Description
size	integer	10	Number of results to return (max: 10000)
from	integer	0	Pagination offset for result set
timeout	string	-	Request timeout (e.g., "30s", "1m")
track_total_hits	boolean	true	Whether to track total hit count accurately
_source	array/object/boolean	true	Controls which fields are returned in results

Field Types

Understanding data types helps with function selection:

Type	Description	Example Functions
text	Full-text searchable strings	String functions, LIKE operations
keyword	Exact-match strings	Equality comparisons, grouping
integer	Whole numbers	Mathematical functions, aggregations
float	Decimal numbers	Mathematical functions, statistical operations
boolean	True/false values	Logical operations, filtering
date	Date/timestamp values	Date functions, time-based grouping
geo_point	Geographic coordinates	Geospatial functions
vector	Dense vector embeddings	Vector similarity functions

Performance Tips

Mathematical Functions

-- Efficient: Use mathematical functions to pre-compute values
SELECT 
  sensor_id,
  ROUND(AVG(temperature), 2) as avg_temp,
  ROUND(SQRT(AVG(POWER(temperature, 2)) - POWER(AVG(temperature), 2)), 2) as std_dev
FROM sensor_readings
GROUP BY sensor_id;

-- Consider: Pre-compute expensive operations when possible
SELECT 
  id,
  value,
  CASE 
    WHEN ABS(value) > 100 THEN 'high'
    WHEN ABS(value) > 50 THEN 'medium'
    ELSE 'low'
  END as magnitude_category
FROM measurements;

Geospatial Optimization

-- Efficient: Filter by bounding box before distance calculations
SELECT *
FROM locations
WHERE latitude BETWEEN 37.0 AND 38.0
  AND longitude BETWEEN -123.0 AND -122.0
  AND ST_DISTANCE(coordinates, ST_POINT(37.7749, -122.4194)) < 10000;

Vector Search Best Practices

-- Efficient: Use appropriate similarity thresholds
SELECT id, title, 
       KNN_SEARCH(query_vector, 'embeddings', 50) as score
FROM documents
WHERE KNN_SEARCH(query_vector, 'embeddings', 50) > 0.7  -- Reasonable threshold
ORDER BY score DESC
LIMIT 10;  -- Limit results for performance

Integration Examples

Combining Multiple Function Types

-- Analytics with mathematical and geospatial functions
SELECT 
  store_id,
  store_name,
  COUNT(*) as order_count,
  AVG(order_value) as avg_order_value,
  ROUND(ST_DISTANCE(
    ST_POINT(store_lat, store_lng),
    ST_POINT(customer_lat, customer_lng)
  ), 2) as avg_delivery_distance
FROM orders o
JOIN stores s ON o.store_id = s.id
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01'
GROUP BY store_id, store_name, store_lat, store_lng
ORDER BY order_count DESC;

-- Content analysis with vector and mathematical functions
SELECT 
  c.category,
  COUNT(*) as document_count,
  AVG(KNN_SEARCH(reference_vector, 'embeddings', 100)) as avg_similarity,
  ROUND(SQRT(
    AVG(POWER(KNN_SEARCH(reference_vector, 'embeddings', 100), 2)) - 
    POWER(AVG(KNN_SEARCH(reference_vector, 'embeddings', 100)), 2)
  ), 3) as similarity_std_dev
FROM documents d
JOIN categories c ON d.category_id = c.id
GROUP BY c.category
ORDER BY avg_similarity DESC;