Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here's a faster <code>data.table</code> solution. The idea is to use the rolling merge functionality of <code>data.table</code>, but before we do that we need to modify the data slightly and make the column <code>x1</code> numeric instead of integer. This is because OP is using strict inequality and to use rolling joins with that we're going to have to decrease the tolerance by a tiny amount, making it a floating point number.</p> <pre><code>my.df[, x1 := as.numeric(x1)] # set the key to x1 for the merges and to sort # (note, if data already sorted can make this step instantaneous using setattr) setkey(my.df, x1) # and now we're going to do two rolling merges, one with the upper bound # and one with lower, then get the index of the match and subtract the ends # (+1, to get the count) my.df[, res := my.df[J(x1 + tol - 1e-6), list(ind = .I), roll = Inf]$ind - my.df[J(x1 - tol + 1e-6), list(ind = .I), roll = -Inf]$ind + 1] # and here's the bench vs @Arun's solution ed = function(my.df) { my.df[, x1 := as.numeric(x1)] setkey(my.df, x1) my.df[, res := my.df[J(x1 + tol - 1e-6), list(ind = .I), roll = Inf]$ind - my.df[J(x1 - tol + 1e-6), list(ind = .I), roll = -Inf]$ind + 1] } microbenchmark(ed(copy(my.df)), ar(copy(my.df))) #Unit: milliseconds # expr min lq median uq max neval # ed(copy(my.df)) 7.297928 10.09947 10.87561 11.80083 23.05907 100 # ar(copy(my.df)) 10.825521 15.38151 16.36115 18.15350 21.98761 100 </code></pre> <p><strong>Note:</strong> as both Arun and Matthew pointed out, if <code>x1</code> is integer, one doesn't have to convert to numeric and subtract a small amount from <code>tol</code> and can use <code>tol - 1L</code> instead of <code>tol - 1e-6</code> above.</p>
    singulars
    1. This table or related slice is empty.
    1. POR grouping by condition in data.table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USeddi
    1. USeddi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POR grouping by condition in data.table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 indeed, this is faster.
      singulars
      1. PO
      1. USArun
    2. CO+1 Can x1 be kept as integer and just +1L and -1L handles strict inequality i.e. J(x1 + tol - 1L)? Should be faster to keep as integer.
      singulars
      1. PO
      1. USMatt Dowle
    3. CO@MatthewDowle we had comments roughly to that effect with @Arun, although I didn't realize that `1L` vs `1` makes a difference, probably should've left them here - the `1L` comparison indeed works for integer `x1`, but fails if the minimum distance between different `x1`'s is less than 1
      singulars
      1. PO
      1. USeddi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload