Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This isn't really an answer so much as an extended comment. I'll remove it if it's deemed not to make sense.</p> <p>If you're trying to find the 'similar' items on the fly, then the problem is that you have a lot (~79k) of Orders to look at. So if you're trying to do this, then you need ways to cut down the number of Orders you're considering before you do the expensive set comparison.</p> <p>One way, pointed out by @Will, is to take into account the <em>number of items</em> in the Orders. So if your target order has 20 items then you only need to consider Orders with 17-23 OrderItems (or something like that, depending upon the exact calculation for '85% similarity'). I'm assuming that these numbers can be calculated via a trigger, whenever an Order is created or changed, and stored in columns on the Order table. </p> <p>But if you can store the size of the set then you could also store other numbers. For instance, you could store the number of <em>odd</em> OrderItem primary key values in each Order. Then the Orders that you're considering would have to be appropriately close to having that number of odd order numbers (I might do the maths at some point to fill out 'appropriately close').</p> <p>And if you think of partitioning the values by 'odd' numbers as splitting into size-1 stripes, you can easily partition by differently sized stripes using the modulus operator. Eg. ItemID%4&lt;2 will made size-2 stripes. You can then record for each Order the number of OrderItem primary keys within those stripes. Your candidate Orders will have to be appropriately close to your target Order on each way of partitioning the values.</p> <p>So what you'll end up with is a big subquery that tries to limit the size of the candidates in the Orders table by looking at a whole bunch of metrics that are stored - and indexed - on that table. </p>
    singulars
    1. This table or related slice is empty.
    1. POHow to select similar sets in SQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USYellowfog
    1. USYellowfog
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    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
    1. This table or related slice is empty.
 

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