Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think this solves your problem.</p> <p>Here is the approach. First, observe that the final data has the product of the number of each attribute -- 2*7*11 = 154 rows. Then observe that each value occurs a fixed number of times. For AttributeId = 16, each value occurs 154 / 2, because there are two values.</p> <p>So, the idea is to calculate the number of times that each value appears. Then, generate the list of all the values. The final challenge is to assign the group numbers to these. For this, I use <code>row_number()</code> partitioned by the attribute id. To be honest, I'm not 100% that the grouping assignment is correct (it makes sense and it passed the eyeball test), but I'm worried that I'm missing a subtlety.</p> <p>Here is the query:</p> <pre><code>with attributecount1 as ( select c.AttributeId, count(*) as cnt from _Combinations c group by c.AttributeId ), const as ( select exp(sum(log(cnt))) as tot, count(*) as numattr from attributecount1 ), attributecount as ( select a.*, (tot / a.cnt) as numtimes from attributecount1 a cross join const ), thevalues as ( select c.AttributeId, c.Value, ac.numtimes, 1 as seqnum from AttributeCount ac join _Combinations c on ac.AttributeId = c.AttributeId union all select v.AttributeId, v.Value, v.numtimes, v.seqnum + 1 from thevalues v where v.seqnum + 1 &lt;= v.numtimes ) select row_number() over (partition by AttributeId order by seqnum, Value) as groupnum, * from thevalues order by 1, 2 </code></pre> <p>The SQL Fiddle is <a href="http://www.sqlfiddle.com/#!6/7fb33/42" rel="nofollow">here</a>.</p> <p>EDIT:</p> <p>Unfortunately, I don't have access to SQL Server today and SQL Fiddle is acting up.</p> <p>The problem is solvable. The above solution works, but -- as stated in my comment -- only when the dimensions are pairwise mutually prime. The problem is the assignment of the group number to the values. It turns out that this is a problem in number theory.</p> <p>Essentially, we want to enumerate the combinations. If there were 2 in two groups, then it would be:</p> <pre><code>group 0: 1 1 group 1: 1 2 group 2: 2 1 group 3: 2 2 </code></pre> <p>You can see a relationship between the group number and which values are assigned -- based on the binary representation of the group number. If this were 2x3, then it would look like:</p> <pre><code>group 0: 1 1 group 1: 1 2 group 2: 1 3 group 3: 2 1 group 4: 2 2 group 5: 2 3 </code></pre> <p>Same idea, but now there is not "binary" representation. Each position in the number would have a different base. No problem.</p> <p>So, the challenge is mapping a number (such as the group number) to each digit. This requires appropriate division and modulo arithmetic.</p> <p>The following implements this in Postgres:</p> <pre><code>with c as ( select 1 as attrid, '1' as val union all select 1 as attrid, '2' as val union all select 2 as attrid, 'A' as val union all select 2 as attrid, 'B' as val union all select 3 as attrid, '10' as val union all select 3 as attrid, '20' as val ), c1 as ( select c.*, dense_rank() over (order by attrid) as attrnum, dense_rank() over (partition by attrid order by val) as valnum, count(*) over (partition by attrid) as cnt from c ), a1 as ( select attrid, count(*) as cnt, cast(round(exp(sum(ln(count(*))) over (order by attrid rows between unbounded preceding and current row))) as int)/count(*) as cum from c group by attrid ), a2 as ( select a.*, (select cast(round(exp(sum(ln(cnt)))) as int) from a1 where a1.attrid &lt;= a.attrid ) / cnt as cum from a1 a ), const as ( select cast(round(exp(sum(ln(cnt)))) as int) as numrows from a1 ), nums as ( select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 from const ), ac as ( select c1.*, a1.cum, const.numrows from c1 join a1 on c1.attrid = a1.attrid cross join const ) select * from nums join ac on (nums.n/cum) % cnt = valnum - 1 order by 1, 2; </code></pre> <p>(Note: generate_series() was not working correctly for some reason with certain joins, which is why it manually generates the sequence of numbers.)</p> <p>When SQL Fiddle gets working again, I should be able to translate this back to SQL Server.</p> <p>EDIT II:</p> <p>Here is the version that works in SQL Server:</p> <pre><code>with attributecount1 as ( select c.AttributeId, count(*) as cnt from _Combinations c group by c.AttributeId ), const as ( select cast(round(exp(sum(log(cnt))), 1) as int) as tot, count(*) as numattr from attributecount1 ), attributecount as ( select a.*, (tot / a.cnt) as numtimes, (select cast(round(exp(sum(log(ac1.cnt))), 1) as int) from attributecount1 ac1 where ac1.AttributeId &lt;= a.AttributeId ) / a.cnt as cum from attributecount1 a cross join const ), c as ( select c.*, ac.numtimes, ac.cum, ac.cnt, dense_rank() over (order by c.AttributeId) as attrnum, dense_rank() over (partition by c.AttributeId order by Value) as valnum from _Combinations c join AttributeCount ac on ac.AttributeId = c.AttributeId ), nums as ( select 1 as n union all select 1 + n from nums cross join const where 1 + n &lt;= const.tot ) select * from nums join c on (nums.n / c.cum)%c.cnt = c.valnum - 1 option (MAXRECURSION 1000) </code></pre> <p>THe SQL Fiddle is <a href="http://www.sqlfiddle.com/#!6/7fb33/61" rel="nofollow">here</a>.</p>
    singulars
    1. This table or related slice is empty.
    1. POTrying to accomplish without dynamic SQL (sql server)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGordon Linoff
    1. USGordon Linoff
    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
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONice! I was working on something similar with `exp(sum(log(cnt)))`. It's a neat trick to do "aggregate" multiplication.
      singulars
      1. PO
      1. USN West
    2. COGordon - very interesting. The row counts match and I've ordered it several ways and as you said it passes the eyeball test. Let me do a few more thorough compares and I'll report back.
      singulars
      1. PO
      1. USAS2012
    3. CO@AS2012 it passes the fiddle test too, for sake of pointless duplication http://sqlfiddle.com/#!6/76cfe/4
      singulars
      1. PO
      1. USJodrell
 

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