I'm struggling with applying the visitor pattern on some objects that have scalar members and at the same time aggregate members (collections). These are the objects that I have: Artist - id - name - .. more scalar values .. - worksOfArt <-- this is a collection as WorkOfArt instances WorkOfArt - id - name - .. more scalar val...
Hi, I've been attempting to aggregate (some what erratic) daily data. I'm actually working with csv data, but if i recreate it - it would look something like this: library(zoo) dates <- c("20100505", "20100505", "20100506", "20100507") val1 <- c("10", "11", "1", "6") val2 <- c("5", "31", "2", "7") x <- data.frame(dates = dates, val1=...
I am using an aggregate initializer to set up a block of static data for a unit test. I would like to use the array size as the expected number of elements, but this can fail if too few initializers are provided: my_struct_type expected[14] = { { 1.234, 0, 'c' }, { 3.141, 1, 'z' }, { 2.718, 0, 'a' } }; This gives no compi...
Do you see a better way to read data from sqlite database into business collections? These are 2 methods of my Repository. I want to get all Schoolclass entites with/without referencing Pupil entities (see Left outer join) Is there anything that I can improve ? Haven`t found any good tips about that scenario in google so I tried mysel...
Hi, i have trouble with following sql-statement (trimmed): SELECT nr, (CASE WHEN SUM(vkdtab.amount*liter)<>0 AND jjjjmm BETWEEN 201001 and 201009 THEN SUM(net)/SUM(vkdtab.amount*liter) ELSE 0 END) as return FROM tab GROUP BY 1,2,3 It should give me the amount/liter of items in a special timeframe, but I...
I have the following query SELECT tagindex, AVG(val) from floatTable WHERE tagindex IN(828,856,883,910) AND DateAndTime > DATEADD(HH,-1,GETDATE()) AND DateAndTime < DATEADD(HH,-2,GETDATE()) group by tagindex It returns the following: 828 1 856 1 883 1 910 1 How can I return a single result where it is the combined a...
In Foursquare, the user who has the highest score for a place in the last N days is awarded the Mayorship of that place. What is the most efficient way to implement that? A user could have checked into hundreds of places. To display all the mayorships that belong to a user, it'd be necessary to go through all those hundreds of places ...
I want to store a tree structure of arbitrary depth in an SQL database (MySQL, but want to avoid DBMS-specific features). Now I want to compute a value N for each node as follows: first, calculate the sum of a certain column in all (immediate) children of the current node which are leaves (i.e. have no children) then, calculate the max...
I'd like to make a comma seperated value string with Linq's Aggregate function. Anyone know how to do this? Given an array of strings like this: var authors = new string[] {"author 1", "author 2", "author 3"}; How do I get a single string like this author 1, author 2, author 3? I'm thinking something like authors.Aggregate(author => ...
enter code hereHow do I get the distinct count the ProdID column in this aggregate query? Dim query As SubSonic.SqlQuery = New SubSonic.Select(SubSonic.Aggregate.GroupBy(TOrders.DelivDateColumn, "DelivDate"),SubSonic.Aggregate.Count(TOrders.ProdIdColumn, "ProdId")).From(of TOrders) If I add Distinct() at the end, its working on the re...
1 Select id,count(*) as totalX FROM my_table WHERE x_factor = 1 GROUP BY id ResultSet: id totalX --------- -------------- 9 34 10 6 11 21 12 3 2 Select id,count(*) as totalY FROM my_table WHERE y_factor = 1 GROUP BY id ResultSet 2: id...
I have two tables, employer and position: Employer eID eName Position eID salary I need to match my eID between the two tables, determine what the max salary is, and print only the eName. Any suggestions as to how I can do this? I have tried multiple ways, but nothing seems to work. I am not sure where to put in the max(salary) ...
Hi When using aggregate, how can I make reference to a previous period within the function? For example if aggregating by days how can make reference to the previous day from the next day in order to calculate the function. For example if I had a one year hourly time series and I like to calculate the maximum of one day minus the mini...
Hello, I have a query that's basically "count all the items of type X, and return the items that exist more than once, along with their counts". Right now I have this: Item.objects.annotate(type_count=models.Count("type")).filter(type_count__gt=1).order_by("-type_count") but it returns nothing (the count is 1 for all items). What am I...
Hi all, Had a quick look through and couldn't find my question again. So I'm hoping someone here can help. I have a large table with a similar structure to the below. DateTime | InboundUserID | OutboundUserID | CustomerUserID | Cost | Mins | Account Number I'm trying to group the above data into hourly chunks and store the result in a...
I have a long time series of daily data and 101 columns. Each month I would like to calculate the cov of each of the first 100 columns with the 101st column. This would generate a monthly covariance with the 101st column for each of the 100 columns based on daily data. It seems that aggregate does what I want with functions that take a s...
How can I write this all in one line? mydata is a zoo series, limit is a numeric vector of the same size tmp <- ave(coredata(mydata),as.Date(index(mydata)),FUN = function(x) ( (cummax(x)-x )) ) tmp <- (tmp < limit) final <- ave(tmp,as.Date(index(mydata)),FUN = function(x) cumprod( x) ) I've tried to use two vectors as argument to ave...
I want to do the following pseudo-SQL: SUM( SELECT a FROM tab WHERE b > 0); This syntax doesn't work (at least not in sqlite), so I'm trying to figure out the proper phrasing. SELECT SUM(a) FROM tab WHERE b > 0 GROUP BY (*); % nope SELECT SUM(a) FROM tab WHERE b > 0 GROUP BY (1); % nope Suggestions? ...
I'm trying to write a Linq query which fetches all users whose first or last name begins with at least one string in a list of strings. This is used for auto-completion of recipients in a messaging system. This was my first naive attempt: var users = UserRepository.ALL() foreach (var part in new [] { 'Ha', 'Ho', 'He' }) { string pa...
Hi, I would like to get a sum from a column, with and without a condition. The code I have now is SELECT regular.id, regular.sum as regularsum, special.sum as specialsum FROM (SELECT Sum(stuff) as sum, id FROM table WHERE commonCondition = true GROUP BY id) as regular INNER JOIN (SELECT Sum(stuff) as sum, id FROM table Where commo...