Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would try something like this for speed, listing orders by similarity to Order @OrderId. The joined INTS is supposed to be the intersection and the similarity value is my attempt to calculate the Jaccard index.</p> <p>I am not using the quantity field at all here, but i think it could be done too without slowing the query down too much if we figure out a way to quantify similarity that includes quantity. Below, I am counting any identical item in two orders as a similarity. You could join on quantity as well, or use a measure where a match that includes quantity counts double. I don't know if that is reasonable.</p> <pre><code>SELECT OI.OrderId, 1.0*COUNT(INTS.ItemId) / (COUNT(*) + (SELECT COUNT(*) FROM OrderItem WHERE OrderID = @OrderId) - COUNT(INTS.ItemId)) AS Similarity FROM OrderItem OI JOIN OrderItem INTS ON INTS.ItemID = OI.ItemID AND INTS.OrderId=@OrderId GROUP BY OI.OrderId HAVING 1.0*COUNT(INTS.ItemId) / (COUNT(*) + (SELECT COUNT(*) FROM OrderItem WHERE OrderID = @OrderId) - COUNT(INTS.ItemId)) &gt; 0.85 ORDER BY Similarity DESC </code></pre> <p>It also presupposes that OrderId/ItemId combinations are unique in OrderItem. I realize this might not be the case, and it could be worked around using a view.</p> <p>I'm sure there are better ways, but one way to weigh in quantify difference be to replace the nominator COUNT(INTS.ItemId) with something like this (supposing all quantities to be positive) that decreases the hit slowly towards 0 when the quantities differ.</p> <pre><code> 1/(ABS(LOG(OI.quantity)-LOG(INTS.quantity))+1) </code></pre> <p>Added: This more readable solution using the Tanimoto Similarity suggested by JRideout</p> <pre><code>DECLARE @ItemCount INT, @OrderId int SELECT @OrderId = 1 SELECT @ItemCount = COUNT(*) FROM OrderItem WHERE OrderID = @OrderId SELECT OI.OrderId, SUM(1.0* OI.Quantity*INTS.Quantity/(OI.Quantity*OI.Quantity+INTS.Quantity*INTS.Quantity-OI.Quantity*INTS.Quantity )) / (COUNT(*) + @ItemCount - COUNT(INTS.ItemId)) AS Similarity FROM OrderItem OI LEFT JOIN OrderItem INTS ON INTS.ItemID = OI.ItemID AND INTS.OrderId=@OrderId GROUP BY OI.OrderId HAVING SUM(1.0* OI.Quantity*INTS.Quantity/(OI.Quantity*OI.Quantity+INTS.Quantity*INTS.Quantity-OI.Quantity*INTS.Quantity )) / (COUNT(*) + @ItemCount - COUNT(INTS.ItemId)) &gt; 0.85 ORDER BY Similarity DESC </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to select similar sets in SQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USdigscoop
    1. USdigscoop
    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
    1. COAnd if there are doubles or quantity 0 somewhere, try going through a view like SELECT OrderId, ItemId, Sum(Quantity) AS Quantity FROM OrderItem OI GROUP BY OrderId, ItemId HAVING Sum(Quantity)>0
      singulars
      1. PO
      1. USdigscoop
    2. COBoth of your assumptions below the code block are true, OrderItem.Quantity > 0, and OrderItem.ItemID + OrderItem.OrderID is the PK.
      singulars
      1. PO
      1. USZachary Yates
    3. COThen this should work. The log formula might be a little naive though.
      singulars
      1. PO
      1. USdigscoop
 

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