SQL-запрос для разделения значения на строки, которые добавляются к итогу в SQL Server

Допустим, у меня есть следующее

Col1         Col2
1            A      
1            A
1            A
1            B
1            B
1            B
1            B
2            A
2            A
2            B
3            A
3            A
3            A
3            A
3            A
3            A

Что мне нужно сделать, это написать запрос, который определяет количество записей для каждой комбинации Col1 и Col2 и разделить 1 на это число и присвоить это значение новому столбцу (Col3), однако мне также нужно обновить записи в некоторых случаях поэтому при суммировании нового столбца он всегда добавляется к единице для каждой комбинации Col1 и Col2.

Итак, на первом этапе я бы получил

Col1         Col2          NumberofRows
1            A             3
1            B             4
2            A             2
2            B             1
3            A             6

что при делении на 1 дает

Col1         Col2         Col3
1            A            0.33
1            A            0.33
1            A            0.33
1            B            0.25
1            B            0.25
1            B            0.25
1            B            0.25
2            A            0.5
2            A            0.5
2            B            1
3            A            0.17
3            A            0.17
3            A            0.17
3            A            0.17
3            A            0.17
3            A            0.17

Однако при суммировании Col3, где Col1 = 1 и Col2 = A, мы получаем 0,99 вместо 1 по очевидным причинам. Точно так же, суммируя Col3, где Col1=3 и Col2=A, мы получаем 1,02.

Как я могу это сделать?


sql rows sql-server sum divide
person Palps    schedule 31.08.2014    source источник

Ответы (3)


arrow_upward
0
arrow_downward

SQL Server может хранить точные числа только в том случае, если они могут быть выражены как целое число, деленное на кратное десяти. Поскольку одна треть не может быть выражена таким образом, SQL Server не может хранить одну треть без потери точности.

Лучшим вариантом было бы сохранить количество строк. Количество строк — это целые числа, и SQL Server может хранить их без потери точности. Они будут в сумме точно соответствовать общему количеству строк. И если вы отображаете строку, вы можете отобразить 1/rc. То, что вы отображаете, никогда не будет суммироваться до единицы, но это ограничение отображения числа в десятичной форме.

Если можно отображать число в виде дроби, вы можете:

select  '1/' + cast(rc as varchar(20))
person Andomar    schedule 31.08.2014

arrow_upward
0
arrow_downward

Я пытался использовать CTE. Пожалуйста, проверьте мой подход ниже:

;WITH c1 
AS
( SELECT col1, col2, count(*) as Cnt
  FROM test
  GROUP BY col1, col2
),
c2 
AS
( SELECT t.col1, t.col2, cast(cast(1 as decimal(5, 2))/grpCount.Cnt as decimal(5, 2))
  AS Cnt, 1%grpCount.Cnt AS roundedCount
  FROM test t
  JOIN (SELECT * FROM c1) AS grpCount
 ON grpCount.col1 = t.col1 AND grpCount.col2 = t.col2
)
SELECT * FROM c2;

Скрипт SQL: - http://sqlfiddle.com/#!6/fe268/7

person Karthik Kola    schedule 31.08.2014

arrow_upward
0
arrow_downward

Вам нужно убедиться, что SQL использует промежуточные значения как числа с плавающей запятой, но затем приводит к десятичной дроби 2 dp при отображении

скрипт SQL

Настройка схемы MS SQL Server 2012:

CREATE TABLE tableA
(
    Col1 int,
    Col2 VARCHAR(1)
)

INSERT INTO tableA 
VALUES
    (1,'A'),
    (1,'A'),
    (1,'A'),
    (1,'B'),
    (1,'B'),
    (1,'B'),
    (1,'B'),
    (2,'A'),
    (2,'A'),
    (2,'B'),
    (3,'A'),
    (3,'A'),
    (3,'A'),
    (3,'A'),
    (3,'A'),
    (3,'A')

Запрос 1:

;WITH cnt
AS
(
    SELECT Col1, Col2, 1.0 / COUNT(*)  cnt
    FROM tableA
    GROUP BY Col1, Col2
)
SELECT A.Col1, A.Col2, CAST(SUM(cnt) AS decimal(4,2))
FROM tableA A
INNER JOIN cnt
    ON A.Col1 = cnt.Col1 AND A.Col2 = cnt.Col2
GROUP BY A.Col1, A.Col2

Результаты:

| COL1 | COL2 | COLUMN_2 |
|------|------|----------|
|    1 |    A |        1 |
|    1 |    B |        1 |
|    2 |    A |        1 |
|    2 |    B |        1 |
|    3 |    A |        1 |
person Steve Ford    schedule 01.09.2014