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.
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.
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.
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
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.
What’s the best workaround for CTEs in MapD and are there plans for their future support?
MapD currently supports the
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
You are correct. Dropping the
LEFT JOIN and changing the
FROM runs great.
FROM medical_claim, member