Support for CTE, derived tables, temporary tables, etc


#1

Is there planned support for temporary tables that only exist for the duration of query? We have many analyses that perform complex calculations using the above. I’d like to avoid rewriting them to be handled in some sort of post-processing step.


#2

HI,

We have beta support for temporary tables currently but it really depends what you are trying to do with them as to if it will be applicable in MapD.

Can you describe the process you are trying to execute with the temp tables. Can you share the queries. If you are fundamentally trying to do ETL in the database we may not be a great match at this stage.

regards


#3

Here is an example query. The idea is to compute a per member per month value by first temporarily calculating the members per month and then using that with the desired metric value.

WITH
    member AS (
            SELECT
                    COUNT(CASE
                            WHEN ((member_month.member_id IS NOT NULL) AND (member_month.medical_eligible = 1)) THEN member_month.member_id
                    END) AS member_month_count
            FROM
                    member_month
            WHERE
                    member_month.service_date BETWEEN '2016-01-01' AND '2016-12-01'
        )
SELECT
        SUM(medical_claim.medical_paid_amount) * CAST(1 AS FLOAT) / NULLIF(member.member_month_count, 0) AS medical_paid_amount_pmpm
FROM
        medical_claim
LEFT JOIN member ON TRUE
WHERE
        member_month.service_date BETWEEN '2016-01-01' AND '2016-12-01'
GROUP BY
        member.member_month_count

This query is built dynamically based on user selections. For example, often filters are provided and end up in the WHERE clause of both queries. It’s possible to break this into two queries and compute the result client side, of course, but doing it all in one go is definitely more convenient.

I hope that helps.


#4

I can’t see temp tables on your query but an inline view called members.

I used inline views on mapd since a long time


#5

Same thing, called Common Table Expression in the SQL standards world. The WITH clause saves you having to rewrite the query if it’s inconvenient to do that in the (possibly multiple) place it would appear in the query.


#6

What’s the best workaround for CTEs in MapD and are there plans for their future support?


#7

Hi,

MapD currently supports the WITH statement.

Are you trying to use it? What error are you seeing?

I suspect from your query above the issue is probably related to the LEFT JOIN ON TRUE syntax

regards


#8

You are correct. Dropping the LEFT JOIN and changing the FROM runs great.

FROM medical_claim, member

Thanks guys!