INNER JOIN with sub select

SQL++
1

Hello everyone. I am having some problems coming up with a way to get a result from an aggregate of historical data. I understand N1QL does not support inner joins with sub selects since it joins on keys only, which makes my job harder.

I was hoping that by explaining what I want to achieve someone could help me come up with a solution - even if it involves multiple queries.

I store some data periodically for users, and I want, for each of this users, get the most recent document that the user has (User A might have a document from September 10th, User B might have a document for September 9th, etc.). After I have this set of documents, I want to calculate the average of a value that is contained in this documents.

I was able to get the most recent document for each user, but I am not able to perform the average on that result.

SELECT DISTINCT b.userId, b
FROM bucket b
WHERE b.docType = "Record" 
ORDER BY b.date DESC

Let me know if you need more information. Thanks!

2

How about this.

SELECT AVG( s.pair[1].someField ) AS avg
FROM (
SELECT MAX( [ doc.date,	doc ] )	AS pair
FROM doc
GROUP BY doc.userId
) AS s
;
1 Like
3

Yes, that worked perfectly. Thank you!

1 Like