첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170

TestProject.zip
다운로드

▶ MainForm.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

namespace TestProject
{
    /// <summary>
    /// 메인 폼
    /// </summary>
    public partial class MainForm : Form
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region CREATE_STUDENT_TABLE_SQL

        /// <summary>
        /// CREATE_STUDENT_TABLE_SQL
        /// </summary>
        private const string CREATE_STUDENT_TABLE_SQL = @"
CREATE TABLE STUDENT_TABLE
(
    NAME  TEXT NOT NULL
   ,SCORE REAL NOT NULL
)
";

        #endregion
        #region INSERT_STUDENT_TABLE_SQL

        /// <summary>
        /// INSERT_STUDENT_TABLE_SQL
        /// </summary>
        private const string INSERT_STUDENT_TABLE_SQL = @"
INSERT INTO STUDENT_TABLE
(
    NAME
   ,SCORE
)
VALUES
(
    @NAME
   ,@SCORE
);
";

        #endregion
        #region SELECT_STUDENT_TABLE_SQL

        /// <summary>
        /// SELECT_STUDENT_TABLE_SQL
        /// </summary>
        private static string SELECT_STUDENT_TABLE_SQL = @"
SELECT
    NAME
   ,AVG(SCORE)     AS AVG
   ,AVERAGE(SCORE) AS AVERAGE
   ,STDEV(SCORE)   AS STDEV
FROM STUDENT_TABLE
GROUP BY NAME;
";

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 생성자 - MainForm()

        /// <summary>
        /// 생성자
        /// </summary>
        public MainForm()
        {
            InitializeComponent();

            this.Load += Form_Load;
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Private
        //////////////////////////////////////////////////////////////////////////////// Event

        #region 폼 로드시 처리하기 - Form_Load(sender, e)

        /// <summary>
        /// 폼 로드시 처리하기
        /// </summary>
        /// <param name="sender">이벤트 발생자</param>
        /// <param name="e">이벤트 인자</param>
        private void Form_Load(object sender, EventArgs e)
        {
            using(SQLiteConnection connection = SQLiteHelper.GetConnection("Data Source=:memory:"))
            {
                connection.Open();

                SQLiteFunction.RegisterFunction(typeof(SQRT));
                SQLiteFunction.RegisterFunction(typeof(AVERAGE));
                SQLiteFunction.RegisterFunction(typeof(STDEV));

                // 학생 테이블을 생성한다.
                SQLiteHelper.Execute(connection, CREATE_STUDENT_TABLE_SQL);

                // 학생 데이터를 추가한다.
                InsertStudentData(connection);

                DataTable resultTable = SQLiteHelper.GetDataTable(connection, SELECT_STUDENT_TABLE_SQL);

                this.dataGridView.DataSource = resultTable;
            }
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////// Function

        #region 학생 데이터 추가하기 - InsertStudentData(connection, name, score)

        /// <summary>
        /// 학생 데이터 추가하기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="name">성명</param>
        /// <param name="score">점수</param>
        /// <returns>처리 행 수</returns>
        private int InsertStudentData(SQLiteConnection connection, string name, double score)
        {
            List<SQLiteParameter> parameterList = new List<SQLiteParameter>()
            {
                new SQLiteParameter { ParameterName = "@NAME" , DbType = DbType.String, Value = name  },
                new SQLiteParameter { ParameterName = "@SCORE", DbType = DbType.Double, Value = score }
            };

            int result = SQLiteHelper.Execute(connection, INSERT_STUDENT_TABLE_SQL, parameterList);

            return result;
        }

        #endregion
        #region 학생 데이터 추가하기 - InsertStudentData(connection)

        /// <summary>
        /// 학생 데이터 추가하기
        /// </summary>
        /// <param name="connection">연결</param>
        private void InsertStudentData(SQLiteConnection connection)
        {
            InsertStudentData(connection, "김철수", 100.0);
            InsertStudentData(connection, "김철수",  95.5);
            InsertStudentData(connection, "김철수",  80.3);
            InsertStudentData(connection, "김철수", 100.0);
            InsertStudentData(connection, "김철수",  90.7);

            InsertStudentData(connection, "이영희", 100.0);
            InsertStudentData(connection, "이영희", 100.0);
            InsertStudentData(connection, "이영희",  80.1);
            InsertStudentData(connection, "이영희",  85.3);
            InsertStudentData(connection, "이영희",  90.5);
        }

        #endregion
    }
}

 

728x90

 

▶ SQRT.cs

using System;
using System.Data.SQLite;

namespace TestProject
{
    /// <summary>
    /// 제곱근 함수
    /// </summary>
    [SQLiteFunction(Arguments = 1, FuncType = FunctionType.Scalar, Name = "SQRT")]
    public class SQRT : SQLiteFunction
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 호출하기 - Invoke(argumentArray)

        /// <summary>
        /// 호출하기
        /// </summary>
        /// <param name="argumentArray">인자 배열</param>
        /// <returns>처리 결과</returns>
        public override object Invoke(object[] argumentArray)
        {
            return Math.Sqrt((double)argumentArray[0]);
        }

        #endregion
    }
}

 

300x250

 

▶ AVERAGE.cs

using System.Data.SQLite;

namespace TestProject
{
    /// <summary>
    /// 평균 함수
    /// </summary>
    [SQLiteFunction(Arguments = 1, FuncType = FunctionType.Aggregate, Name = "AVERAGE")]
    public class AVERAGE : SQLiteFunction
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 카운트
        /// </summary>
        private int count = 0;

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 단계별 처리하기 - Step(argumentArray, stepNumber, contextData)

        /// <summary>
        /// 단계별 처리하기
        /// </summary>
        /// <param name="argumentArray">인자 배열</param>
        /// <param name="stepNumber">단계 변호</param>
        /// <param name="contextData">컨텍스트 데이터</param>
        public override void Step(object[] argumentArray, int stepNumber, ref object contextData)
        {
            if(contextData == null)
            {
                contextData = 0.0;

                this.count = 0;
            }

            contextData = (double)contextData + (double)argumentArray[0];

            this.count++;
        }

        #endregion
        #region 최종 처리하기 - Final(contextData)

        /// <summary>
        /// 최종 처리하기
        /// </summary>
        /// <param name="contextData">컨텍스트 데이터</param>
        /// <returns>결과</returns>
        public override object Final(object contextData)
        {
            return (double)contextData / count;
        }

        #endregion
    }
}

 

▶ STDEV.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;

namespace TestProject
{
    /// <summary>
    /// 표준 편차 함수
    /// </summary>
    [SQLiteFunction(Arguments = 1, FuncType = FunctionType.Aggregate, Name = "STDEV")]
    public class STDEV : SQLiteFunction
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 소스 리스트
        /// </summary>
        private List<double> sourceList = new List<double>();

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 단계별 처리하기 - Step(argumentArray, stepNumber, contextData)

        /// <summary>
        /// 단계별 처리하기
        /// </summary>
        /// <param name="argumentArray">인자 배열</param>
        /// <param name="stepNumber">단계 변호</param>
        /// <param name="contextData">컨텍스트 데이터</param>
        public override void Step(object[] argumentArray, int stepNumber, ref object contextData)
        {
            if(contextData == null)
            {
                contextData = 0;

                this.sourceList.Clear();
            }

            this.sourceList.Add((double)argumentArray[0]);
        }

        #endregion
        #region 최종 처리하기 - Final(contextData)

        /// <summary>
        /// 최종 처리하기
        /// </summary>
        /// <param name="contextData">컨텍스트 데이터</param>
        /// <returns>결과</returns>
        public override object Final(object contextData)
        {
            if(this.sourceList.Count == 1)
            {
                return double.NaN;
            }

            double average = this.sourceList.Average();

            double minusSquareSummary = 0.0;

            foreach(double source in sourceList)
            {
                minusSquareSummary += (source - average) * (source - average);
            }

            double stdev = Math.Sqrt(minusSquareSummary / (sourceList.Count - 1));

            return stdev;
        }

        #endregion
    }
}
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요