如何基于含账龄、连接及临时表的应收款SQL查询创建视图并用于Power BI

Hey there! Let's break down your questions step by step—they build on each other, so we'll start with the basics before diving into your specific accounts receivable scenario.

1. How to Create a Basic SQL View Query #

Views are virtual tables that pull data from one or more underlying tables using a stored query—they don’t store data themselves (unless you’re using a database-specific materialized view). Here’s how to build one:

Basic Syntax #

CREATE VIEW [ViewName] AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• Use CREATE OR REPLACE VIEW if you need to update an existing view without dropping it first (works in most databases like PostgreSQL, SQL Server, MySQL).

Example #

Suppose you want a view that shows active customers from your customers table:

CREATE VIEW ActiveCustomers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE is_active = 1;

You can query the view just like a regular table:

SELECT * FROM ActiveCustomers;

2. Creating an Accounts Receivable View with Ageing, Joins, and CTEs (Replacing Temp Tables) #

Your existing AR query uses temp tables, joins, and ageing calculations—but views can’t reference temp tables (since temp tables are session-specific and disappear when your session ends). Instead, replace temp tables with Common Table Expressions (CTEs) to encapsulate intermediate logic, then wrap the whole thing into a view.

Step 1: Rewrite Temp Table Logic as CTEs #

Let’s say your original query looks like this (using temp tables):

-- Original temp table setup
CREATE TABLE #TempInvoiceData AS
SELECT 
    i.invoice_id,
    i.customer_id,
    i.invoice_date,
    i.total_amount,
    COALESCE(SUM(p.payment_amount), 0) AS total_paid,
    i.total_amount - COALESCE(SUM(p.payment_amount), 0) AS outstanding_balance
FROM invoices i
LEFT JOIN payments p ON i.invoice_id = p.invoice_id
GROUP BY i.invoice_id, i.customer_id, i.invoice_date, i.total_amount;

-- Calculate ageing and join with customers
SELECT
    c.customer_name,
    t.invoice_id,
    t.outstanding_balance,
    DATEDIFF(day, t.invoice_date, GETDATE()) AS days_outstanding,
    -- Categorize ageing buckets
    CASE
        WHEN DATEDIFF(day, t.invoice_date, GETDATE()) <= 30 THEN '0-30 Days'
        WHEN DATEDIFF(day, t.invoice_date, GETDATE()) <= 60 THEN '31-60 Days'
        ELSE 'Over 60 Days'
    END AS ageing_bucket
FROM #TempInvoiceData t
JOIN customers c ON t.customer_id = c.customer_id
WHERE t.outstanding_balance > 0;

Step 2: Convert to a View with CTEs #

Replace the temp table with a CTE, then wrap everything in a CREATE VIEW statement:

CREATE VIEW AR_Ageing_Summary AS
WITH TempInvoiceData AS (
    SELECT 
        i.invoice_id,
        i.customer_id,
        i.invoice_date,
        i.total_amount,
        COALESCE(SUM(p.payment_amount), 0) AS total_paid,
        i.total_amount - COALESCE(SUM(p.payment_amount), 0) AS outstanding_balance
    FROM invoices i
    LEFT JOIN payments p ON i.invoice_id = p.invoice_id
    GROUP BY i.invoice_id, i.customer_id, i.invoice_date, i.total_amount
)
SELECT
    c.customer_name,
    t.invoice_id,
    t.outstanding_balance,
    DATEDIFF(day, t.invoice_date, GETDATE()) AS days_outstanding,
    CASE
        WHEN DATEDIFF(day, t.invoice_date, GETDATE()) <= 30 THEN '0-30 Days'
        WHEN DATEDIFF(day, t.invoice_date, GETDATE()) <= 60 THEN '31-60 Days'
        ELSE 'Over 60 Days'
    END AS ageing_bucket
FROM TempInvoiceData t
JOIN customers c ON t.customer_id = c.customer_id
WHERE t.outstanding_balance > 0;

• Note: DATEDIFF and GETDATE() are SQL Server-specific functions. Adjust for your database: use DATE_DIFF in PostgreSQL, TIMESTAMPDIFF in MySQL, etc.

Step 3: Use the View in Power BI #

Once the view is created in your database, here’s how to use it in Power BI:

• Open Power BI Desktop and click Get Data > Select your database type (e.g., SQL Server, PostgreSQL).
• Enter your server/database credentials, then navigate to the AR_Ageing_Summary view in the database object list.
• Load the view into Power BI—you’ll see all columns (including the ageing_bucket and days_outstanding filter columns) available in the Data pane.
• Use the filter columns to build visuals: for example, add a slicer for ageing_bucket to filter AR data by time buckets, or use outstanding_balance in a bar chart grouped by customer.

Pro Tips #

• Ensure the user account connecting Power BI to the database has SELECT permissions on the view.
• If your view is slow, consider adding indexes on the underlying tables’ join columns (e.g., invoice_id, customer_id) or using a materialized view (if your database supports it) for pre-computed results.

内容的提问来源于stack exchange，提问作者Gaurav Maggu