Databases
StackOverflow2013
Postsdbo
POMulti-Column Integer Ordering
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMulti-Column Integer Ordering
    primarykey
    data
    text
    <p>I don't know if I've chosen the appropriate title for this question (if not, please change it accordingly) but consider the following simplified table structure I'm working with:</p> <pre><code>---------------------------------------------- | date | i | j | k | x | y | z | ---------------------------------------------- | 100209 | 1 | 2 | 3 | 4 | 5 | 6 | ---------------------------------------------- | 100210 | 2 | 3 | 4 | 5 | 6 | 7 | ---------------------------------------------- | 100211 | 0 | 1 | 2 | 3 | 4 | 5 | ---------------------------------------------- | 100212 | 1 | 2 | 3 | 4 | 5 | 6 | ---------------------------------------------- | 100213 | 6 | 5 | 4 | 3 | 2 | 1 | ---------------------------------------------- </code></pre> <p><code>i</code>, <code>j</code>, <code>k</code>, <code>x</code>, <code>y</code>, <code>z</code> are all unrelated integers / floats, they all represent different factors and can have very different orders of magnitude (<em><code>i</code> can range from 1 - 10 while <code>j</code> can range from 100 - 1000</em>).</p> <p>I'm trying to select dates that share similar conditions; Given a set of <code>i</code>, <code>j</code>, <code>k</code>, <code>x</code>, <code>y</code>, <code>z</code> values I need to <strong>return all results ordered by <em>closeness</em> of all values as a whole</strong> for instance, if <code>i = 1</code>, <code>j = 2</code>, <code>k = 3</code>, <code>x = 4</code>, <code>y = 5</code> and <code>z = 6</code> the query should return the following dates in this order:</p> <ol> <li><em>100209</em></li> <li><em>100212</em></li> <li><strong>100210</strong></li> <li><strong>100211</strong></li> <li>100213</li> </ol> <p><s>I'm not sure if this is relevant or not to the question, but some values (<code>i</code>, <code>j</code>, <code>k</code>) mean <em>more is better</em> while other values (<code>x</code>, <code>y</code>, <code>z</code>) mean the opposite: <em>less is better</em>.</s></p> <p><strong>How I should build such a query? Is this possible with SQL alone?</strong></p> <hr> <h1>@Pentium10:</h1> <p>I'll try to answer your comment the best way I can. Here is a sample of my data:</p> <pre><code>--------------------------------------------------------------------------------- date | temperature | humidity | pressure | windSpeed | moonDistance --------------------------------------------------------------------------------- 090206 | 7 | 87 | 998.8 | 3 | 363953 --------------------------------------------------------------------------------- ...... | ... | ... | .... | ... | ...... --------------------------------------------------------------------------------- 100206 | 10 | 86 | 1024 | 2 | 386342 --------------------------------------------------------------------------------- 100207 | 9 | 90 | 1015 | 1 | 391750 --------------------------------------------------------------------------------- 100208 | 13 | 90 | 1005 | 2 | 396392 --------------------------------------------------------------------------------- 100209 | 12 | 89 | 1008 | 2 | 400157 --------------------------------------------------------------------------------- 100210 | 11 | 92 | 1007 | 3 | 403012 --------------------------------------------------------------------------------- 100211 | 6 | 86 | 1012 | 2 | 404984 --------------------------------------------------------------------------------- 100212 | 6 | 61 | 1010 | 3 | 406135 --------------------------------------------------------------------------------- 100213 | 7 | 57 | 1010 | 2 | 406542 --------------------------------------------------------------------------------- </code></pre> <p>My table structure has more columns and thousands of rows but hopefully this will be enough to get my point clear. I'm not going to attempt to order these values like I did in my previous example because I would probably get it wrong, but I basically need to do two types of queries with this data:</p> <ol> <li><strong><em>show me all dates, ordered by the resemblance of the conditions provided by me</em></strong></li> <li><strong><em>show me all dates, ordered by the resemblance of the conditions observed in date X</em></strong></li> </ol> <p>I understand that the second query can easily be archived by using the first one, but my problem lies in sorting by resemblance using several columns, that's what I meant by "<strong>ordered by <em>closeness</em> of all values as a whole</strong>". As in, if I was dealing with only one column it would be a lot easier to order by <em>likeness</em> but my head really starts spinning when dealing with several columns.</p> <p><strong>The goal is to to be able to produce results like this:</strong></p> <blockquote> <p>Today is really similar to d/m/yy, very similar to d/m/yy, somewhat similar to d/m/yy, ...</p> </blockquote> <p>In my case I'm working with weather and atmospheric data but if it helps I guess you can think in this problem in terms of <code>employees</code> (having <code>attendance</code>, <code>hoursPerWeek</code> and <code>monthlySalary</code> columns) and order by employees that most closely resemble <code>attendance = 100%</code>, <code>hoursPerWeek = 40</code> and <code>monthlySalary = $5000</code>, for instance.</p> <p><strong><em>PS:</strong> Now that I've given this employees example I'm not really sure anymore if it can be compared to the weather example I'm working with, since with the employees table you can compute (<code>rating = monthlySalary / hoursPerWeek * attendance</code> for instance) and kinda weight the columns, but I don't think the same can be done with the weather table - <strong>input is appreciated anyhow</strong>.</em></p> <p><strong><em>PS2</strong>: I'm not sure if I expressed myself well enough, if you still have doubts please let me know.</em></p> <hr> <blockquote> <p><strong>Bounty</strong></p> <p>Some good suggestions so far, however none of them truly solve my problem. I'm setting up a bounty to hopefully gather even more possible solutions to this problem. Thanks.</p> </blockquote>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlix Axel
    1. USAlix Axel
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMulti-Column Integer Ordering
      1. USSarfraz
      1. VTFavorite
    2. VO
      singulars
      1. POMulti-Column Integer Ordering
      1. USAlix Axel
      1. VTFavorite
    3. VO
      singulars
      1. POMulti-Column Integer Ordering
      1. This table or related slice is empty.
      1. VTUpMod
    1. COa language barrier issue, can you explain what you mean `all values as a whole`, and give more example having other ranges like 100-1000
      singulars
      1. POMulti-Column Integer Ordering
      1. USPentium10
    2. CO@Pentium10: I've updated my question to address your comment, please check it again, thanks. =)
      singulars
      1. POMulti-Column Integer Ordering
      1. USAlix Axel
    3. COThank you. you did a great job. As you mentioned rating for weather data is kinda hard. See my updated answer below.
      singulars
      1. POMulti-Column Integer Ordering
      1. USPentium10
 

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