Databases
StackOverflow2013
Postsdbo
POChanging the format of a value based on a function
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POChanging the format of a value based on a function
    primarykey
    data
    text
    <p>Using SQL, I'm trying to group data in a table in 3 dimensions and for that I need a way to modify a value that has been outputted based on some grouping criteria. I then plan to use the GROUP command to finally group the rows together.</p> <p>It's easier to describe with an example.</p> <p>I run</p> <pre><code>SELECT b as b_bin , l as l_bin , k_1AperMag1 as k1_bin , count(*) as num FROM gpssource WHERE b between -1.1 and 1.1 and l between 9.9 and 11.6 and k_1AperMag1 between 0 and 25 GROUP BY b, l, k_1AperMag1 </code></pre> <p>and get a table</p> <pre><code>b_bin | l_bin | k1_bin | num -1.0115976238979311 | 0.968853586216488 | 13.680575 | 1 -1.0299234557450931 | 11.591887279330654 | 16.761375 | 1 -0.3757959886541285 | 11.446711966673629 | 16.372013 | 1 -0.7250531677528679 | 11.357881248039163 | 17.16921 | 1 ...and about 100 million more rows </code></pre> <p>I basically need to bin each row into groups for b_bin, l_bin and k1_bin. For example, the 1st row needs to go into a b bin -1.1 -> -1.0, l bin 0.9 -> 1.0 and k1 bin 13 -> 14. My idea to do this in SQL is to change -1.0115976238979311 to -1.05, 0.968853586216488 to 0.95 and 13.680575 to 13.5 and then use the GROUP by command to actually group them into bins and get a count for each set of bins.</p> <p>My end result would then be:</p> <pre><code>b_bin | l_bin | k1_bin | num -1.05 | 0.95 | 13.5 | 23 -0.95 | 0.95 | 13.5 | 20 -0.95 | 0.85 | 13.5 | 25 -0.95 | 0.85 | 12.5 | 23 and more, for each bin in b, l and k1. </code></pre> <p>Eventually, I put it into a matrix and create an image for it with b and l being x and y coordinates and k1 being a slice in 3d space, which leads to something like this (which was created by binning the data in python): <img src="https://i.stack.imgur.com/b6HbE.png" alt="density map of the galaxy"></p> <p>Unfortunately, I'm not exactly sure how to do the change from the actual value to the bin's center value. Is this even possible to achieve with SQL? I have all this working in python but I need to use SQL to bin the data for logistical reasons.</p> <p>So my basic question is: how do you modify a value that has been printed out by SELECT. I can use format() to format it in a number of ways but as far as I can tell, it doesn't let me pick my own value to output... Does SQL even have functions? I googled for a while and couldn't find much on the matter...</p> <p>Any pointers would be much appreciated!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDurand
    1. USDurand
    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. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POChanging the format of a value based on a function
      1. USSeasoned
      1. VTFavorite
    1. COWhat engine are you using for sql: SQL Server, MySQL, Oracle...
      singulars
      1. POChanging the format of a value based on a function
      1. USAdam Wenger
    2. COWhat are the sizes of the Bins for each? You mention .1 increments for B and L but K is 1 (13-14)
      singulars
      1. POChanging the format of a value based on a function
      1. USAdam Wenger
    3. COI'm not exactly sure to be honest. I'd guess that it is MySQL but it could also be MS SQL. I'd have to ask around. Would the syntax differ a lot? The bin sizes are constant for the whole data set but they will most likely differ between b, l and k1. I can set them to all be 0.1 but that's not particularly future proof. Thanks for the help!
      singulars
      1. POChanging the format of a value based on a function
      1. USDurand
 

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