CODEWITHSUNDEEP
neo4jcypher
user3517958
user3517958

Reputation: 31

Return an aggregated columnar result set in Neo4j

How would I return an aggregated sum of customers' hourly data in a column representing each hour of the day? That question might be a bit vague, so I'll set the context...

I have a data set of Customers, each Customer has a meter, that meter is read every hour (think of an electrical meter for your house). Customers 1-X are assigned to Group 1, Customers Y-Z are assigned to Group 2, etc. I have setup a time tree in Neo4j (Year-->Month-->Day-->Hour) and the hourly meter reads are a separate node with an edge to the appropriate Hour (and an edge to the Customer). I need to return a report that sums up all of the hourly meter reads for all Customers in each Group (by Group), but each hour needs to be a separate column, like this:

GroupName Date H1Sum H2Sum H3Sum…H24Sum

The following query returns the correct format of the report but only for the first hour. How would I create an additional 23 columns of data representing hours 2-24?

MATCH (Group:LMRGroup)<-[:PART_OF_GROUP]-(SubGroup:SubLMRGroup)<-[:PART_OF_GROUP]-(c:Customer)-[:HAS_METER_READ]->(HrlyMR:HourlyMeterRead)-[:METER_READ]->(hr:Hour
{hour:1})<-[:HAS_HOUR]-(d:Day {day:5})<-[HAS_DAY]-(m:Month {month:3})<-[:HAS_MONTH]-(y:Year {year:2015})
RETURN 
Group.Name as GroupName,
m.month + '-' + d.day + '-' + y.year as Date,
sum(HrlyMR.Reading) as HE1

Thanks for the help and my apologies if this is still a confusing question.

Upvotes: 2

Views: 104

Answers (1)

FrobberOfBits
FrobberOfBits

Reputation: 18002

That's quite a query. I've broken it up to clarify.

MATCH (Group:LMRGroup)<-[:PART_OF_GROUP]-
      (SubGroup:SubLMRGroup)<-[:PART_OF_GROUP]-
      (c:Customer)-[:HAS_METER_READ]->
      (HrlyMR:HourlyMeterRead)-[:METER_READ]->
      (hr:Hour)<-[:HAS_HOUR]-
      (d:Day {day:5})<-[HAS_DAY]-
      (m:Month {month:3})<-[:HAS_MONTH]-
      (y:Year {year:2015})
RETURN 
   distinct(hr.hour) as Hour, 
   Group.Name as GroupName,
   m.month + '-' + d.day + '-' + y.year as Date,
   sum(HrlyMR.Reading) as HE1;

I removed your parameter on (hr:Hour { hour: 1}) so that it matches all hours. Then you just return distinct(hr.hour) to group records by hour 1, hour 2, hour 3, and so on. The rest of the results should aggregate to the appropriate hour automatically, no need for a GROUP BY statement as you might otherwise use in SQL

Upvotes: 1

Related Questions