July 17, 2024 at 8:11 pm
CREATE TABLE dbo.tab1
(
idrow INT NOT null IDENTITY,
group1 VARCHAR(10) NOT NULL,
c1 NUMERIC(10,2) NOT NULL,
c2 NUMERIC(10,2) NOT NULL
)
GO
INSERT INTO tab1 (
group1, --group
c1, --numerator
c2 --denominator
)
VALUES
('1', 1, 2),
('1', 2, 1.4),
('1', 3, 5.2),
('2', 1, 0.6),
('2', 4, 0.5),
('2', 3, 0.9)
to store the proportions, the numerator and denominator are stored in the table (there may be several for one group)
. For each group of such values, you need to get the total values in the same form: separately the numerator and separately the denominator
for example, for group 1, the correct answer will be as follows:
group1 c1 c2
1 36.48 14.56
because:
1/2 + 2/1.4 + 3/5.2 = according to the rules of school mathematics, we bring to a common denominator = 1 * 1.4 * 5.2 / (2 * 1.4 * 5.2) + 2 * 2 * 5.2 / 1.4 * 2 * 5.2 + 3 * 2 * 1.4 / 5.2 * 2 * 1.4 = (7.28 + 20.8 + 8.4) / 14.56 = 36.48 / 14.56
I hope I didn't make any mistakes in the calculation
or if, say, the result is values that can be shortened, such as:
group1 c1 c2
1 30 10
then it would be good (although not necessarily) to reduce them and get:
group1 c1 c2
1 3 1
something like the smallest common divisor (or the largest common multiple, I've forgotten since school days)
how to solve this in sql?
I can create ugly code and solve this, but maybe there is a better option?
I don't know how to multiply a variable number of values when there may be only 3 or many of them (it's easy to sum them up later).
that's how I tried to do it, but it's wrong, because you need to multiply other numbers, it doesn't get any further:
I would like to avoid recursive cte, but if not, then ok...
SELECT *, t1.c1 * t2.c1
FROM tab1 AS t1
JOIN tab1 AS t2 ON t1.group1 = t2.group1 AND t1.idrow <> t2.idrow
WHERE t1.group1 = '1'
July 18, 2024 at 8:42 am
This took a bit of head scratching. I managed to solve it, assuming that there are a max of 3 parts per group (as per your example).
I then added a potential 2 extra per group, and commented the code out, so that you can see how to expand it if you ever get more parts per group.
DECLARE @tab1 table (
idrow int NOT NULL IDENTITY
, group1 varchar(10) NOT NULL
, c1 numeric(10, 2) NOT NULL
, c2 numeric(10, 2) NOT NULL
);
INSERT INTO @tab1(
group1 --group
, c1 --numerator
, c2 --denominator
)
VALUES ( '1', 1, 2.0 ), ( '1', 2, 1.4 ), ( '1', 3, 5.2 )
, ( '2', 1, 0.6 ), ( '2', 4, 0.5 ), ( '2', 3, 0.9 );
WITH cteBase AS (
SELECT t1.group1
, idrow1 = t1.idrow
, t1.c1
, idrow2 = t2.idrow
, t2.c2
, rn = ROW_NUMBER() OVER (PARTITION BY t1.group1, t1.idrow ORDER BY t2.idrow)
FROM @tab1 AS t1
INNER JOIN @tab1 AS t2 ON t1.group1 = t2.group1
)
, cteGroup AS (
SELECT b.idrow1
, b.group1
, num = CASE WHEN b.idrow1 = b.idrow2 THEN b.c1 ELSE NULL END
, den1 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 1 THEN b.c2 ELSE NULL END
, den2 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 2 THEN b.c2 ELSE NULL END
, den3 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 3 THEN b.c2 ELSE NULL END
-- , den4 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 4 THEN b.c2 ELSE NULL END
-- , den5 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 5 THEN b.c2 ELSE NULL END
FROM cteBase AS b
)
, cteNumerator AS (
SELECT g.group1
, g.idrow1
, num = ISNULL(CAST(MAX(g.num ) AS numeric(10, 2)), 1.00)
* ISNULL(CAST(MAX(g.den1) AS numeric(10, 2)), 1.00)
* ISNULL(CAST(MAX(g.den2) AS numeric(10, 2)), 1.00)
* ISNULL(CAST(MAX(g.den3) AS numeric(10, 2)), 1.00)
-- * ISNULL(CAST(MAX(g.den4) AS numeric(10, 2)), 1.00)
-- * ISNULL(CAST(MAX(g.den5) AS numeric(10, 2)), 1.00)
, den1 = CAST(MAX(g.den1) AS numeric(10, 2))
, den2 = CAST(MAX(g.den2) AS numeric(10, 2))
, den3 = CAST(MAX(g.den3) AS numeric(10, 2))
-- , den4 = CAST(MAX(g.den4) AS numeric(10, 2))
-- , den5 = CAST(MAX(g.den5) AS numeric(10, 2))
FROM cteGroup AS g
GROUP BY g.group1, g.idrow1
)
SELECT n.group1
, num = SUM(n.num)
, den = ISNULL(MAX(n.den1), 1.00)
* ISNULL(MAX(n.den2), 1.00)
* ISNULL(MAX(n.den3), 1.00)
-- * ISNULL(MAX(n.den4), 1.00)
-- * ISNULL(MAX(n.den5), 1.00)
FROM cteNumerator AS n
GROUP BY n.group1
ORDER BY n.group1;
July 18, 2024 at 9:55 am
Thank you for help!
Now I see clearly that recursive cte should be used here to support unknown number of parts in each group without changing the code.
July 18, 2024 at 12:48 pm
according to the rules of school mathematics, we bring to a common denominator
How to calculate the product of all of the denominators for each group? Why doesn't SQL Server have a PRODUCT function? Seems like an oversight. It would be nice to claim credit for how to do this but ChatGPT-4o suggested to use logarithms/exponents and it seemed like a good idea. According to the rules of school mathematics logarithms convert multiplication into addition and exponents do the opposite. Back in the day conversion tables were kept in books. Once the common denominators are calculated (in the first cte) the TotalNumerator's and TotalDenominator's are summed in the 2nd cte. The outer query OUTER APPLY's any greatest common denominator (gcd) using dbo.fnTally to count up to the lower of the totaled (c1, c2) tuple values and checking the modulo's. Here's a link to an article with the code for dbo.fnTally by Jeff Moden
drop TABLE if exists #tab1;
go
CREATE TABLE #tab1(
idrow INT NOT null IDENTITY,
group1 VARCHAR(10) NOT NULL,
c1 NUMERIC(10,2) NOT NULL,
c2 NUMERIC(10,2) NOT NULL);
INSERT INTO #tab1 (group1, c1, c2) VALUES
('1', 1, 2), ('1', 2, 1.4),
('1', 3, 5.2), ('2', 1, 0.6),
('2', 4, 0.5), ('2', 3, 0.9);
-- Step 1: Calculate the product of all denominators for each group
WITH DenominatorProduct AS (
SELECT group1,
EXP(SUM(LOG(c2))) AS TotalDenominator
FROM #tab1
GROUP BY group1
),
-- Step 2: Calculate the adjusted numerators and sum them up for each group
SummedValues AS (
SELECT t.group1,
SUM(t.c1 * dp.TotalDenominator / t.c2) AS TotalNumerator,
dp.TotalDenominator AS TotalDenominator
FROM #tab1 t
JOIN DenominatorProduct dp ON t.group1 = dp.group1
GROUP BY t.group1, dp.TotalDenominator
)
-- Step 3: Reduce the fraction to its simplest form using OUTER APPLY and fnTally
SELECT sv.group1,
sv.TotalNumerator,
sv.TotalDenominator,
sv.TotalNumerator / gcd.GCDValue AS ReducedNumerator,
sv.TotalDenominator / gcd.GCDValue AS ReducedDenominator
FROM SummedValues sv
OUTER APPLY (SELECT MAX(N) AS GCDValue
FROM dbo.fnTally(1, CASE WHEN sv.TotalNumerator < sv.TotalDenominator
THEN sv.TotalNumerator
ELSE sv.TotalDenominator END)
WHERE CAST(sv.TotalNumerator AS BIGINT) % N = 0
AND CAST(sv.TotalDenominator AS BIGINT) % N = 0) gcd
order by sv.group1;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 18, 2024 at 3:56 pm
Thank you but I didn't find the code of fnTally on the link. I also didn't understand how to use log/exp functions to solve this task.
Regarding the use of log and exponent.
EXP(SUM(LOG(c2))) AS TotalDenominator
The inner function LOG(c2) converts c2 to it's log representation. As the innermost function it converts c2 for each row. SUM sums the converted LOG(c2)'s across the rows of the GROUP BY column(s). Then EXP converts the summed logs into the product of all of the c2's. I asked ChatGPT-4o to create an example script in SQL Server and here's what it came up with
-- Properties of Logarithms
-- 1. Logarithm of a Product:
-- The logarithm of a product is the sum of the logarithms of the factors:
-- log(a * b) = log(a) + log(b)
--
-- 2. Exponentiation:
-- The exponential function is the inverse of the logarithm function:
-- exp(log(x)) = x
-- Example with Two Numbers
-- Let's say we have two numbers, 4 and 5, and we want to calculate their product using logarithms.
-- Step 1: Convert to Logarithms
-- Calculate the natural logarithm of each number:
-- log(4) ≈ 1.386
-- log(5) ≈ 1.609
-- Step 2: Sum the Logarithms
-- Add the logarithms together:
-- log(4) + log(5) ≈ 1.386 + 1.609 = 2.995
-- Step 3: Convert Back Using Exponentiation
-- Use the exponential function to convert the sum back to the product:
-- exp(2.995) ≈ 20
-- SQL Example
-- Create a temporary table with two numbers
DROP TABLE IF EXISTS #temp;
GO
CREATE TABLE #temp (
Value NUMERIC(10, 2));
-- Insert the two numbers into the temporary table
INSERT INTO #temp (Value) VALUES (4), (5);
-- Calculate the product using the EXP(SUM(LOG(Value))) trick
SELECT EXP(SUM(LOG(Value))) AS Product
FROM #temp;
To get dbo.fnTally maybe there's a link at the end of the article? Here's the one I'm using
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/ (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 22, 2024 at 9:04 am
us26 wrote:according to the rules of school mathematics, we bring to a common denominator
How to calculate the product of all of the denominators for each group? Why doesn't SQL Server have a PRODUCT function? Seems like an oversight. It would be nice to claim credit for how to do this but ChatGPT-4o suggested to use logarithms/exponents and it seemed like a good idea. According to the rules of school mathematics logarithms convert multiplication into addition and exponents do the opposite. Back in the day conversion tables were kept in books. Once the common denominators are calculated (in the first cte) the TotalNumerator's and TotalDenominator's are summed in the 2nd cte. The outer query OUTER APPLY's any greatest common denominator (gcd) using dbo.fnTally to count up to the lower of the totaled (c1, c2) tuple values and checking the modulo's.
Man, I hated those darn log books. Who would have though that 40 years later, they'd come back to haunt me.
July 30, 2024 at 7:53 pm
Ha, a haunting could be a good thing 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply