🏠 Home / Hub

🐘 PostgreSQL Lesson 04 — Functions & Aggregates

← Back to PostgreSQL Menu  |  🏠 Hub

1. Aggregate Functions

-- COUNT, SUM, AVG, MIN, MAX
SELECT
  COUNT(*)           AS total_users,
  COUNT(phone)       AS with_phone,    -- NULL ကို မရေမ
  SUM(price * qty)   AS revenue,
  AVG(price)         AS avg_price,
  MIN(price)         AS cheapest,
  MAX(price)         AS most_expensive
FROM orders;

-- GROUP BY
SELECT
  role,
  COUNT(*) AS user_count,
  MAX(created_at) AS latest_join
FROM users
GROUP BY role
ORDER BY user_count DESC;

-- HAVING — GROUP BY result ကို filter
SELECT
  user_id,
  COUNT(*) AS post_count,
  SUM(views) AS total_views
FROM posts
GROUP BY user_id
HAVING COUNT(*) >= 5     -- 5+ posts ရတဲ့ users ပဲ
ORDER BY total_views DESC;

-- ROLLUP — totals with subtotals
SELECT region, department, SUM(salary)
FROM employees
GROUP BY ROLLUP(region, department);

2. Window Functions (Analytics)

-- Window functions = GROUP BY မတူ — each row ကို retain ထားပြီး aggregate
-- OVER() = window definition

-- ROW_NUMBER
SELECT
  name,
  salary,
  department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;
-- Result: each employee with their rank within their department

-- RANK / DENSE_RANK (ties)
SELECT name, score,
  RANK()       OVER (ORDER BY score DESC) AS rank_with_gaps,   -- 1,1,3,4
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank_no_gaps      -- 1,1,2,3
FROM results;

-- LAG / LEAD (access previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Moving average (7-day)
SELECT
  date,
  revenue,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_sales;
💡 Window functions = PostgreSQL ရဲ့ most powerful analytics feature — MySQL မှာ limited!

3. Date & Time Functions

-- Current time
SELECT NOW();              -- 2024-01-15 10:30:00.123+06
SELECT CURRENT_DATE;       -- 2024-01-15
SELECT CURRENT_TIME;       -- 10:30:00.123+06

-- Extract parts
SELECT
  EXTRACT(YEAR FROM created_at)  AS year,
  EXTRACT(MONTH FROM created_at) AS month,
  EXTRACT(DOW FROM created_at)   AS day_of_week,   -- 0=Sunday
  DATE_TRUNC('month', created_at)                   AS month_start
FROM users;

-- Date arithmetic
SELECT
  NOW() + INTERVAL '7 days'    AS next_week,
  NOW() - INTERVAL '1 month'   AS last_month,
  AGE(birthday)                AS age_interval,
  DATE_PART('year', AGE(birthday))::INTEGER AS age_years
FROM users;

-- Format
SELECT TO_CHAR(created_at, 'DD Mon YYYY HH24:MI') FROM users;
-- 15 Jan 2024 10:30

-- Group by month (reporting)
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;

4. Custom Functions (PL/pgSQL)

-- Create function
CREATE OR REPLACE FUNCTION get_full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;

-- Use it
SELECT get_full_name('Ko', 'Ko');   -- 'Ko Ko'

-- Function with query
CREATE OR REPLACE FUNCTION get_user_post_count(p_user_id INTEGER)
RETURNS INTEGER AS $$
DECLARE
  post_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO post_count
  FROM posts
  WHERE user_id = p_user_id AND published = true;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

-- Trigger function — auto update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_users
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();
💡 updated_at auto trigger = every UPDATE automatically sets updated_at = NOW() — ကိုယ်တိုင် SET မပြင်ရ

← PostgreSQL 03  |  Next: PostgreSQL 05 → Indexes →

📌 Study Checklist