melvintcs Ответов: 2

эффективный способ расчета баланса с помощью sql - запроса

каков самый быстрый/эффективный способ решить эту проблему?

Таблица: транзакция
ID     user        date             deposit         withdraw
1       A        2014-01-21           100              0
2       A        2014-01-21             0             30
3       A        2014-01-21           300              0
4       A        2014-01-23             0             20
5       B        2014-01-23            20              0


Ожидаемый результат: (фильтр по пользователю = 'A' и дате <= '2014-01-24')
   Date          B/F         Deposit      Withdraw     Balance
2014-01-21         0           400          -30          370
2014-01-22       370             0            0          370
2014-01-23       370             0          -20          350
2014-01-24       350             0            0          350


Примечание: Первоначально я разработал базу данных транзакций, объединив "депозит" и "вывод" в "сумму" и добавив новое поле с именем "тип", где " D "указывает на "депозит", а " W "- на "вывод". Но я считаю, что это требует большего расчета, чтобы выйти с результатом.

Пожалуйста, поправьте меня, если я ошибся.
1. Какой дизайн базы данных является более эффективным?
2. Как насчет запроса? должен ли я выполнять вычисления внутри запроса?(Пожалуйста, предоставьте пример запроса, если это возможно) или я читаю строку за строкой и делаю расчет в коде позади? я использую c#, asp.net
_______________ОБНОВЛЕННЫЙ_________________________________________________
мне удалось занять первый ряд, как насчет остальных?
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    Label lblBF = (Label)e.Row.FindControl("lblBF");
    Label lblDeposit = (Label)e.Row.FindControl("lblDeposit");
    Label lblWithdraw = (Label)e.Row.FindControl("lblWithdraw");            
    Label lblTotal = (Label)e.Row.FindControl("lblTotal");

    decimal d_bf = 0;
    // code to run query
    //"SELECT IFNULL(SUM(deposit- withdraw), 0) Balance FROM transaction WHERE date BETWEEN '2013-01-01' AND '2013-12-31'");
    if (dataReader.Read())
    {
        d_bf = Convert.ToDecimal(dr["balance"].ToString());
    }
    ConnectionClose();
        
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        if (e.Row.RowIndex == 0) //calculation for 1st row
        {
            lblBF.Text = d_bf.ToString();
            lblTotal.Text = Convert.ToString(Convert.ToDecimal(lblBF.Text) + Convert.ToDecimal(lblDeposit.Text) - Convert.ToDecimal(lblWithdraw.Text));
        }

        else //calculation for the rest
        {
            // how to get prev row/cell
        }
                        
        if (lblWithdraw.Text != "0.00")
        {
            lblWithdraw.Text = "- " + lblWithdraw.Text;
        }
    }
}

Suk@nta

их нет в колонке б/ф, которую вы упоминаете. можете ли вы написать, где вы получили

melvintcs

простите, я ошибся. Б/ф означает "переносит вперед", остаток предыдущего дня переносит на сегодняшний день. 21 января-это "0", потому что депозит еще не сохранен. 22 января-это "370" , потому что 100+300-30 на 21 января.

Suk@nta

проверьте решение 2

2 Ответов

Рейтинг:
1

Dave Kreskowiak

Я думаю, что ты думаешь об этом неправильно. Столбец B/F не имеет никакого отношения к вычислению. Текущий баланс-это сумма всех депозитов и, с учетом данных, которые вы показываете, сумма всех снятий, сложенных вместе. Если суммы в столбце "снятие средств" являются значениями позиций, то их следует вычесть из депозитов.

SELECT (SUM(Deposit) + SUM(Withdrawl)) As Balance FROM whateverTable


Текущий баланс, который вы показываете, - это вещь пользовательского интерфейса, а не вычисляемая базой данных. Там вам придется выполнять вычисления, строка за строкой, в вашем коде C#.

Sergey Alexandrovich Kryukov

Согласитесь, 5.
—СА

melvintcs

пожалуйста, смотрите мой обновленный вопрос :)

[no name]

Нет, вам нужно задать другой вопрос вместо того, чтобы изменять существующий вопрос, добавляя совершенно отдельные вопросы. И вы должны быть готовы ответить на вопрос: "почему вы не можете написать цикл для циклического просмотра записей?".

melvintcs

я думаю, что даже я обновил свой вопрос. это все еще в теме "эффективный способ для sql-запроса рассчитать баланс". я выполнил функцию цикла for; мой код для i==0 такой же, как и для e.Row.RowIndex == 0, как и выше, но для второй записи они обнаруживают ячейку[4] как NULL :)

[no name]

Как ты себе это представляешь? Эти два вопроса не имеют никакого отношения друг к другу. Один вопрос-это расчет, а другой-зацикливание? Как именно вы полагаете, что они каким-то образом связаны?

Рейтинг:
0

Suk@nta 

// i have tested above table and find a solution , try below query it works fine

WITH CTE AS (
SELECT  
rownum = ROW_NUMBER() OVER (ORDER BY D.Date),D.Date as Date,D.BF as     BF,D.Deposite,D.Withdraw,D.balance
  FROM (
  select date as Date ,0 as BF,sum(deposite) as Deposite,-1*sum(withdraw) as Withdraw, SUM(deposite)-SUM(withdraw) as balance from testTable where Username='A' and date<=CONVERT(SMALLDATETIME,'24/01/2014',103)   group by date)D)

   select Date, ISNULL(BF,0) as 'B/F',Deposite,Withdraw,ISNULL(BF,0)+Deposite+Withdraw as Balance
   from 
   (
    select Date ,ISNULL(BF,0)+(select balance from  CTE prev where prev.rownum = CTE.rownum - 1) as BF ,Deposite,Withdraw,balance as Balance from CTE 
    )x


//не забудьте принять ответ, если вы обнаружите, что он помогает

Dave Kreskowiak

Это может сработать, но я должен поставить под сомнение эффективность ваших решений. Как это работает на множестве из миллиона записей? Мне было бы очень любопытно посмотреть план выполнения этого.

Suk@nta

я не знаю, является ли это эффективным или нет

melvintcs

может ли "с" syxtax использоваться на mysql? я получаю ошибку с "WITH"

Suk@nta

извините, что ключевое слово "WITH" не поддерживается в mysql. эта функция была запрошена с 2006 года. кроме этого другой сервер базы данных принимает