Questions tagged [aggregate]

1

votes
1

answer
24

Views

How to query an array for different combinations of ANDs and ORs in MongoDB Aggregation?

I am trying to search for list of students who have completed courses using different combinations of Logical ANDs and ORs. For example I want to get student who completed ('Course 1' and 'Course 2') or 'Course 3' Here is my data structure for each student : { '_id' : ObjectId('5c68841cb6b18f31975c...
Badis Merabet
1

votes
2

answer
35

Views

Mongodb aggregates: how to project a formatted filter

I think it's not a difficult question but I'm not sure how to do it My collection is [ { type:'bananas', weight:'1'}, { type:'bananas', weight:'10'}, { type:'apple', weight:'5'} ] The result I would like to have is the count of each type in the same query, result expected: { bananas: 2, apple: 1 } W...
François Richard
1

votes
2

answer
408

Views

How to run an aggregate query via mongo_go_driver that has javascript in it?

I'm using the mongo-go-driver (https://godoc.org/github.com/mongodb/mongo-go-driver/mongo) and I'm trying to do the equivalent of db.getCollection('mycollection').aggregate([ { $lookup: { from: 'anothercollection', localField: '_id', foreignField: 'foreignID', as: 'matched_docs' }}, { $match: { 'ma...
Andrew Springman
1

votes
4

answer
61

Views

SQL select count with multiple cases

I want a column where I count transactions where itemtype contains either one of two specified values or location contains one of two specified values. The first two columns (itemtype and location) gives correct figures, but the third seems to count all transactions, irrespective of itemtype or loca...
Klas Blomberg
1

votes
2

answer
33

Views

How to convert hourly data to 6/12/24 hourly data

I am new to here and also R. For example I have this kind of rainfall data. As is seen in the data for a day, I have hourly rainfall data and I want to convert this hourly data to 6-hourly, 12-hourly and daily datas like the sample. How can I achieve this in R?
4

votes
0

answer
18

Views

parent children hierarchy mongodb aggregation pipeline

My Documents in collection: /* 1 createdAt:4/22/2019, 4:53:32 PM*/ { '_id' : ObjectId('5cbdab3c9bd57541040342a1'), 'created_on' : '2017-03-07T21:07:04.778Z', 'feature' : 'Source', 'feature_option' : 'write', 'icon' : 'fa fa-plus', 'is_deleted' : false, 'level' : 2, 'modified_on' : '2017-03-07T21:07:...
Abdul Moiz
1

votes
0

answer
76

Views

How to send List of Expression to Aggregate Function in Spark JAVA API

I'm trying to execute below code using Spark JAVA api. sampleDS=sampleDS .select(col('column1'),col('column2'),col('price1')col('price2')) .groupBy(col('column1'),col('column2')) .agg(expr('sum(price1)').as('MainPrice'),expr('sum(price2)').as('ExtPrice')) .sort(col('column1'),col('column2')); But t...
John Humanyun
1

votes
1

answer
57

Views

SQL simplified group by and average (flatten rows aggregation based on multiple columns)

I have three tables that are all linked through an 'id' column. When all three joined, it looks somewhat like this: +----+-------+--------+-------------------+---------+ | id | Color | T1Data | distinct_value | T3_data | +----+-------+--------+-------------------+---------+ | 1 | green | ab...
HSchmachty
1

votes
1

answer
140

Views

Direct arguments in PostgreSQL user-defined aggregate functions

I am creating a user-defined aggregate function that needs an additional parameter. More precisely it is a cumulative (aka window) minimum that takes as second parameter a time interval defining the window. Since the aggregate function operates on my user-defined data types I have conveyed a dummy e...
Esteban Zimanyi
1

votes
1

answer
53

Views

How to use aggregate and sum while writing to MongoDB?

I'm complete beginner with mongodb, javascript and coding overall, this part of my project is giving me hard times. I have read manuals in mongodb websites, tried look from here practical example for my case, but with bad results. Wanted action is to read values to variables from webpage and write...
Jiikoo
1

votes
0

answer
296

Views

MongoDB: $unwind before $group or after $group?

I have a collection of items that have many subitems and arrays. On page load I want to show first item with it's first subitems and other items with only their names displayed. To sort subitems I use $unwind. Should I $unwind at the beginning of the query or after I have grouped the items Performan...
yodalr
1

votes
0

answer
251

Views

Time-dependent rank autocorrelation in pandas

I have a MultiIndex pandas DataFrame of this schematic form (although the real dataframe I'm working with has millions of rows): import pandas as pd df = pd.DataFrame([['Alpha', 'a', 1,10], ['Alpha', 'a', 2,20],['Alpha', 'a', 3,30], ['Alpha', 'b', 1,50],['Alpha', 'b', 2,60],['Alpha', 'b', 3,10], ['A...
Jon
1

votes
2

answer
735

Views

COUNT() OVER possible using DISTINCT and WINDOWING IN HIVE

I want to calculate the number of distinct port numbers that exist between the current row and the X previous rows (sliding window), where x can be any integer number. For instance, If the input is: ID PORT 1 21 2 22 3 23 4 25 5 25 6 21 The outpu...
alejo
1

votes
1

answer
22

Views

Aggregating Month of Timestamps by User

I have a number of rows in a MySQL table, each with a username (string) and date (datetime) column. I am trying to aggregate the rows by month and year - so my desired output would be like so: Jan-17 Feb-17 ... Dec-18 User1 5 7 2 User2 3 10 6 The way I was pre...
BAK
1

votes
0

answer
50

Views

MongoDB: How to find only customers ordered?

I have 2 collection Customers and Order with sample data as below **Customers** { '_id' : 1, 'name' : 'John', 'sex' : 'male' } { '_id' : 2, 'name' : 'Sara', 'sex' : 'female'} { '_id' : 3, 'name' : 'Jack', 'sex' : 'male'} { '_id' : 4, 'name' : 'David', 'sex' : 'male'} **Orders** { '_id' : 1, customer...
Thang Luu
66

votes
3

answer
35.7k

Views

Multiple aggregations of the same column using pandas GroupBy.agg()

Given the following (totally overkill) data frame example import pandas as pd import datetime as dt df = pd.DataFrame({ 'date' : [dt.date(2012, x, 1) for x in range(1, 11)], 'returns' : 0.05 * np.random.randn(10), 'dummy' : np.repeat(1, 10) }) is there an existing built-in way to apply two...
ely
1

votes
1

answer
166

Views

Sorting array, and selecting field in MongoDB using Java

I'm trying to transform the following data using MongoDB aggregation. I want the array 'connections' sorted and I only want the array-elements that have a name matching my regex. In this case I want the array sorted on 'steps' (number of intermediare friends) and only those with 'Han' in their name...
Kadetje
1

votes
0

answer
45

Views

How to pass user defined variable as argument to function/aggregate in Cassandra 3.0

Table/Type Structure: Create type typ_pks_details( tpks_value Text, tpks_date Text, tpks_comp_flg Text ); Create Table pk_summary ( pks_nbr_key text, pks_type_value text, pks_type_character text, pks_details map, PRIMARY KEY (pks_nbr_key,pks_type_val) ); Function/Aggregate: Create FUNCTION sfunc_co...
lakksv
1

votes
1

answer
63

Views

Fetch records with MAX values between specific timestamp in accurate MAX order

I am having some challenges spooling out some database records. I need to get the rows with MAX value for a specific column and these records must fall between two timestamp values. Here is the SQL query SELECT id, MAX(amount), created FROM `product` where author = '1' AND (created BETWEEN '2018-0...
Ous
1

votes
1

answer
32

Views

MongoDB $lookup Value Back into Array

I have a document with multiple items, that need to lookup further information from another collection to fill out the data. Main Document: { _id: ObjectID('5a30ff41af58f911946b122e'), SalesItems: [ { SalesItemID: 1139, Quantity: 2, Amount: 3.00 }, { SalesItemID: 2549, Quantity: 1, Amount: 5.40 } ]...
DanielWoodward
1

votes
0

answer
52

Views

Strange behavior of pandas DataFrame.agg

The Lambdas in the following code return the same Series, but the aggregation results are different. Why? import pandas as pd df=pd.DataFrame([1, 2]) print(df) print(df.agg({0: lambda x: x.cumsum()})) print(df.agg({0: lambda x: pd.Series([1, 3], name=0)})) Which gives: 0 0 1 1 2 0 0 1 1 3 0 0...
V. Ayrat
1

votes
1

answer
23

Views

pandas: Using built-in and customized aggregation function together?

I used the following code: s=df.groupby('version').agg({'duration':['mean','std'],'ts':['min','max']).reset_index() s.columns=s.columns.map('_'.join) The results work fine. Then I tried to add one more aggregate function quantile(.25) s=df.groupby('version').agg({'duration':['mean','std', quantile(....
Edamame
1

votes
0

answer
414

Views

MongoDB query in java for array fields using ProjectionOperation

Below is the JSON structure for a Store document: { { '_id':'87348378', 'name': 'ABC store', 'type': 'Books', 'books': [ { 'name': 'love', 'id': '1', 'types':{ 'type':'love', 'number':'1' } }, { 'name': 'coreman', 'id': '2', 'types':{ 'type':'love', 'number':'1' } } ] }, { '_id':'87348', 'name': 'So...
RAMESHNAIDU
1

votes
1

answer
31

Views

How do I reference a column that uses an aggregate function in the outer table inside a case statement?

I get this error when executing this query: '#1111 - Invalid use of group function' SELECT c.`id_c`, week(date) AS `week`, YEAR(date) AS `year`, COUNT(mood) AS `moodNumber`, AVG(mood) AS `moodAverage`, IF(COUNT(mood) > 2, SUM(CASE WHEN AVG(m.`mood`) > 3 THEN 1 ELSE 0 END) , 0) AS `usersGoodMood` F...
Randy
1

votes
1

answer
542

Views

Mongo 3.6.3 java driver aggregate hint - returns undefined field 'hint'

I am trying to pass a hint on an aggregate in MongoDB Java Driver 3.6.3. The aggregate API allows for adding a hint as in: MongoCollection coll = database.getCollection('myCollection') ArrayList docList = new ArrayList(); BasicDBObject hint = new BasicDBObject('$hint','reportjob_customerId_1_siiDocu...
Jeff Mason
1

votes
1

answer
32

Views

Values in single column of dataframe by a certains conditions

I have two dataframes and I want to 'combine them by a certains conditions'. My first dataframe have average Sms(avgSms) of day of week(1=Sunday, 2=Monday, 3=Tuesday...) , hour and min: df1: I | Day_of_week | hour | min | AvgSms #1 1 0 0 12 #2 1 0...
john
1

votes
1

answer
41

Views

Aggregation.group(“”).sum(“”) How to write the calculation formula?

This is the Mongo script db.servicer_online_time.aggregate( [ { $group: { _id : '$csId' , totalTime: { $sum: { $multiply: ['$offlineTime', '$onlineTime'] } } } }, ] ); This is Java code GroupOperation groupOperation = Aggregation.group('csId').sum('$multiply: [\'$offlineTime\', \'$onlineTime\']').as...
王金鹏
1

votes
0

answer
65

Views

Are there aggregate initialization differences between std::vector and std::array?

In std::array, we can simply state like this std::array arr_gcc{ 'abcdefghi', 'abcdefghi' }; Or even in clang with -Wall, they just need somewhat more explicit expression, having to add some more subobject braces like this std::array arr_clang_wall_favor{{ {'abcdefghi'}, {'abcdefghi'} }}; When it c...
sandthorn
1

votes
0

answer
38

Views

Mongoose eachAsync Maximum Call Stack Size Exceeded

I'm implementing an aggregate for a collection of 35000 elements and I get Maximum Call Stack Size Exceeded in my sever. It works well on my computer. Here is the code: let query = []; if (!lodash.isEmpty(conditions)) { query.push({$match: conditions}); } query.push( {$lookup: { from: 'projects', lo...
Yamila
1

votes
0

answer
16

Views

using MongoDb aggregation how to get the output another modal

hello my question is I am getting last four months records from database.i got the output but not getting my expected output so how to do this can any one help me to solve this. db.collection.aggregate([ {$match: {CREATE_DATE:{$lte:new Date(), $gte:new Date(new Date().setDate(new Date().getDate(...
Naresh G
1

votes
3

answer
1.2k

Views

How to use HAVING Clause With SUM() Function

So I've got to a point where I have a table with a Username and a SummedHourCount column. My goal is to select the username where (SummedHourCount / SUM(SummedHourCount)) > 3/4. SUM(SummedHourCount) meaning all of the hours present in the column. For example, if I have the following data: Username...
TheEggSample
1

votes
2

answer
37

Views

Calculating mean of several values based on parameters in other columns [duplicate]

This question already has an answer here: Using dplyr to summarize by multiple groups 2 answers Get the means of sub groups of means in R 4 answers I have a data frame like the following: df
Keshav M
1

votes
0

answer
40

Views

ElasticSearch aggregation with nested document

I'm trying to migrate a statistic application from pure MySQL to MySQL + ElasticSearch v6 and I cannot find how to do correctly my aggregation. Because an example is better than smocky explanations, here are extracts of my data: The mapping: { 'mappings': { 'sample_type': { 'properties': { 'hash': {...
jmleroux
1

votes
0

answer
42

Views

Why postgres is showing more rows count in the analyze statement, than actual rows in the table?

I am querying count of all the records in the table, using following query :- groups=# select count(*) from auth_user; count --------- 1000000 (1 row) Following is the schema for the above table:- groups=# \d auth_user; Table 'public.auth_user' Column | Type |...
1

votes
0

answer
26

Views

methods of aggregating data into a single collection

I am currently researching which Database to use for a software, and I have selected MongoDB however I cannot find any ways of aggregating data into a single collection. The two products and reviews collections can be aggregated together into a single collection, but what are the methods in aggregat...
Adnan Ahmed
1

votes
1

answer
37

Views

Split-apply-combine with aggregate : can the applied function accept multiple arguments that are specified variables of the original data?

Some context: On my quest to improve my R-code I'm trying to replace my for-loops whenever I can by R's apply-class functions. The question: Are R's apply functions such as sapply, tapply, aggregate, etc. useful for applying functions that are more complicated in the sense that they take as argument...
Denis Gontcharov
1

votes
0

answer
59

Views

Remove duplicates values in aggregation with limit in Mongo

I have a dataset (an example): {u'geometry': {u'type': u'Point', u'coordinates': [151.5162, -9.44365]}, u'_id': ObjectId('5ad70f71f2119236741ffb39'), u'type': u'Feature', u'properties': {u'POS_ID': u'592795', u'STATUS': u'0', u'TIMESTAMP': u'2013-12-31 18:52:00.000', u'MMSI': u'636015725'}} {u'geome...
Antonis
1

votes
0

answer
458

Views

Mongo returns: Aggregate has empty pipeline

I am trying to get a random image from my db. I try to do that using a aggregate query: exports.findRandomImage = (req, res) => { Image.aggregate({ $sample: { size: 1 } }, function (error, image) { if (error) { console.error(error) } res.send({ title: image.title, description: image.description, url...
joedoesnotknow
1

votes
0

answer
42

Views

Structured Streaming aggregating without grouping

For a project we need to analyse streamed prices (market data) on possible anomalies. We require the 'Average' and the 'Standard Deviation' of those incoming prices over a specific time period. Our approach is with the 'Structured Streaming' api. The problem is: We have to aggregate over each window...
Interfearence
1

votes
1

answer
200

Views

Use dynamic dateRange to filter data and aggregate resulting dataframe by a dynamic selectInput

I'm developing a shinydashboard based on the following sample data frame: Date Year Month Week Wday Day Time ActiveEnergy 2/1/2009 2009 2 5 Sun 1 0 55.36111 3/1/2009 2009 3 9 Sun 1 0 9.334444 4/1/2007 2007 4 14 Sun 1 0 41.27667 6/1/2008 2008 6 22 S...
Nell

View additional questions