<p>출처1 :&nbsp;<a href="https://dba.stackexchange.com/questions/80789/mysql-sum-interval-dates" target="_blank">https://dba.stackexchange.com/questions/80789/mysql-sum-interval-dates</a></p><p>출처2 : <a href="https://dba.stackexchange.com/questions/169186/mysql-MERGE-OR-split-DATETIME-intervals-START-DATE-TO-END-DATE" target="_blank" class="tx-link">https://dba.stackexchange.com/questions/169186/mysql-MERGE-OR-split-DATETIME-intervals-START-DATE-TO-END-DATE</a></p><p>출처3 :&nbsp; <a href="https://rextester.com/EIJOI20983" target="_blank" class="tx-link">https://rextester.com/EIJOI20983</a></p><p><br></p><p>아래와 같은 데이터를 병합해서 보여주고 싶을 때가 있다.&nbsp;</p><p>Id 1, 2를 보면 1번 안에 2번이 포함된다.</p><p style="text-align: left; clear: none; float: none;"><img src='/api/file/11EB0D04F8A1863597400242AC110002' style="max-width:100%;height:auto;"></p><p><br></p><p style="text-align: left; clear: none; float: none;"></p><p>원하는 결과물은 아래와 같다.</p><p style="text-align: left; clear: none; float: none;"><img src='/api/file/11EB0D04F8A4618697400242AC110002' style="max-width:100%;height:auto;"></p><p></p><p><br></p><p>다음과 같이 테스트를 해보자.</p><p><br></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><br><div><span style="color: #6a9955;">-- make test data</span></div><div><span style="color: #569cd6;">DROP</span> <span style="color: #569cd6;">TABLE</span> <span style="color: #569cd6;">IF</span>  <span style="color: #569cd6;">EXISTS</span> LogTable;</div><div><span style="color: #569cd6;">CREATE</span> <span style="color: #569cd6;">TABLE</span> <span style="color: #569cd6;">IF</span> <span style="color: #569cd6;">NOT</span> <span style="color: #569cd6;">EXISTS</span> LogTable (</div><div>    Id <span style="color: #569cd6;">INT</span> <span style="color: #569cd6;">PRIMARY</span> <span style="color: #569cd6;">KEY</span> <span style="color: #569cd6;">NOT</span> <span style="color: #569cd6;">NULL</span> AUTO_INCREMENT,</div><div>    UserId <span style="color: #569cd6;">VARCHAR</span>(<span style="color: #b5cea8;">45</span>),</div><div>    StartTime <span style="color: #569cd6;">DATETIME</span>,</div><div>    EndTime <span style="color: #569cd6;">DATETIME</span></div><div>);</div><br><div><span style="color: #569cd6;">INSERT</span> <span style="color: #569cd6;">INTO</span> LogTable (UserId, StartTime, EndTime) <span style="color: #569cd6;">VALUES</span></div><div>(<span style="color: #ce9178;">'me'</span>,<span style="color: #ce9178;">'2017-04-03 11:00'</span>,<span style="color: #ce9178;">'2017-04-03 11:30'</span>),</div><div>(<span style="color: #ce9178;">'me'</span>,<span style="color: #ce9178;">'2017-04-03 11:10'</span>,<span style="color: #ce9178;">'2017-04-03 11:15'</span>),</div><div>(<span style="color: #ce9178;">'me'</span>,<span style="color: #ce9178;">'2017-04-03 23:45'</span>,<span style="color: #ce9178;">'2017-04-04 00:15'</span>),</div><div>(<span style="color: #ce9178;">'me'</span>,<span style="color: #ce9178;">'2017-04-04 10:00'</span>,<span style="color: #ce9178;">'2017-04-04 11:00'</span>),</div><div>(<span style="color: #ce9178;">'me'</span>,<span style="color: #ce9178;">'2017-04-04 10:30'</span>,<span style="color: #ce9178;">'2017-04-04 11:30'</span>),</div><div>(<span style="color: #ce9178;">'me'</span>,<span style="color: #ce9178;">'2017-04-05 23:00'</span>,<span style="color: #ce9178;">'2017-04-05 23:30'</span>),</div><div>(<span style="color: #ce9178;">'me'</span>,<span style="color: #ce9178;">'2017-04-05 23:15'</span>,<span style="color: #ce9178;">'2017-04-07 00:45'</span>),</div><div>(<span style="color: #ce9178;">'you'</span>,<span style="color: #ce9178;">'2017-04-05 21:00'</span>,<span style="color: #ce9178;">'2017-04-05 23:30'</span>);</div><br><div><span style="color: #6a9955;">-- start end merge</span></div><div><span style="color: #569cd6;">SELECT</span> UserId, <span style="color: #dcdcaa;">MIN</span>(StartTime) <span style="color: #569cd6;">AS</span> StartTime, EndTime, TIMESTAMPDIFF(MINUTE, <span style="color: #dcdcaa;">MIN</span>(StartTime), EndTime) <span style="color: #569cd6;">AS</span> <span style="color: #569cd6;">TIME</span></div><div><span style="color: #569cd6;">FROM</span> (</div><div>    <span style="color: #569cd6;">SELECT</span> x.UserId, x.StartTime, <span style="color: #dcdcaa;">MIN</span>(y.EndTime) <span style="color: #569cd6;">AS</span> EndTime </div><div>    <span style="color: #569cd6;">FROM</span> LogTable <span style="color: #569cd6;">AS</span> X </div><div>    <span style="color: #569cd6;">JOIN</span> LogTable <span style="color: #569cd6;">AS</span> Y </div><div>        <span style="color: #569cd6;">ON</span> x.UserId = y.UserId </div><div>       <span style="color: #569cd6;">AND</span> x.StartTime &lt;= y.EndTime </div><div>       <span style="color: #569cd6;">AND</span> <span style="color: #569cd6;">NOT</span> <span style="color: #569cd6;">EXISTS</span> (</div><div>           <span style="color: #569cd6;">SELECT</span> <span style="color: #b5cea8;">1</span> </div><div>           <span style="color: #569cd6;">FROM</span> LogTable <span style="color: #569cd6;">AS</span> z </div><div>           <span style="color: #569cd6;">WHERE</span> y.UserId = z.UserId </div><div>             <span style="color: #569cd6;">AND</span> y.EndTime &gt;= z.StartTime </div><div>             <span style="color: #569cd6;">AND</span> y.EndTime &lt; z.EndTime</div><div>             <span style="color: #6a9955;">-- 추가적으로 그룹바이 조건을 넣고 싶다면(1)</span></div><div>       ) </div><div>    <span style="color: #569cd6;">WHERE</span> <span style="color: #569cd6;">NOT</span> <span style="color: #569cd6;">EXISTS</span> (</div><div>        <span style="color: #569cd6;">SELECT</span> <span style="color: #b5cea8;">1</span> </div><div>        <span style="color: #569cd6;">FROM</span> LogTable <span style="color: #569cd6;">AS</span> u </div><div>        <span style="color: #569cd6;">WHERE</span> x.UserId = u.UserId </div><div>          <span style="color: #569cd6;">AND</span> x.StartTime &gt; u.StartTime </div><div>          <span style="color: #569cd6;">AND</span> x.StartTime &lt;= u.StartTime</div><div>          <span style="color: #6a9955;">-- 추가적으로 그룹바이 조건을 넣고 싶다면(2)</span></div><div>    ) </div><div>    <span style="color: #6a9955;">-- 정리하고 싶은 데이터에 조건을 넣고 싶다면 (and 를 붙이고 조건 추가)</span></div><div>    <span style="color: #6a9955;">-- AND ...</span></div><br><div>    <span style="color: #6a9955;">-- 추가적으로 그룹바이 조건을 넣고 싶다면(3) Group by 순서에 맞게 기술</span></div><div>    <span style="color: #569cd6;">GROUP BY</span> x.UserId, x.StartTime</div><div>) <span style="color: #569cd6;">AS</span> v <span style="color: #569cd6;">GROUP BY</span> UserId, EndTime;</div><div><br></div></div><p><br></p><p><br></p><p><br></p><hr><p><br></p><p>하다보니 날짜별로 자르고 싶어진다.&nbsp;</p><p><br></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #6a9955;">--=========================================================================</span></div><div><span style="color: #6a9955;">--&nbsp;분단위로&nbsp;계산</span></div><div><span style="color: #6a9955;">--=========================================================================</span></div><div><span style="color: #569cd6;">SET</span>&nbsp;time_zone&nbsp;=&nbsp;<span style="color: #ce9178;">'Asia/Seoul'</span>;</div><div><span style="color: #569cd6;">SET</span>&nbsp;time_zone&nbsp;=&nbsp;<span style="color: #ce9178;">'UTC'</span>;</div><div><span style="color: #569cd6;">select</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;WorkDate,&nbsp;<span style="color: #dcdcaa;">sum</span>(WorkMin)&nbsp;min_activity</div><div><span style="color: #569cd6;">from</span>&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">date</span>(StartTime)&nbsp;WorkDate,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">if</span>(<span style="color: #569cd6;">date</span>(StartTime)&nbsp;=&nbsp;<span style="color: #569cd6;">date</span>(EndTime),&nbsp;time_to_sec(timediff(EndTime,&nbsp;StartTime))&nbsp;/&nbsp;<span style="color: #b5cea8;">60</span>,&nbsp;(<span style="color: #b5cea8;">1440</span>&nbsp;-&nbsp;time_to_sec(<span style="color: #569cd6;">time</span>(StartTime))&nbsp;/&nbsp;<span style="color: #b5cea8;">60</span>))&nbsp;WorkMin</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">from</span>&nbsp;tss_log</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">UNION&nbsp;ALL</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">date</span>(EndTime)&nbsp;WorkDate,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">if</span>(<span style="color: #569cd6;">date</span>(StartTime)&nbsp;=&nbsp;<span style="color: #569cd6;">date</span>(EndTime),&nbsp;<span style="color: #b5cea8;">0</span>,&nbsp;time_to_sec(<span style="color: #569cd6;">time</span>(EndTime))&nbsp;/&nbsp;<span style="color: #b5cea8;">60</span>)&nbsp;WorkMin</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">from</span>&nbsp;tss_log</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">UNION&nbsp;ALL</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;&nbsp;&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vDate&nbsp;WorkDate,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #dcdcaa;">sum</span>(<span style="color: #b5cea8;">1440</span>)&nbsp;WorkMin</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">from</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vcm_date</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">join</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tss_log</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">on</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vcm_date.vDate&nbsp;&gt;&nbsp;<span style="color: #569cd6;">date</span>(StartTime)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">and</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vcm_date.vDate&nbsp;&lt;&nbsp;<span style="color: #569cd6;">date</span>(EndTime)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">where</span>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #dcdcaa;">datediff</span>(EndTime,&nbsp;StartTime)&nbsp;&gt;&nbsp;<span style="color: #b5cea8;">1</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">group&nbsp;by</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;vDate</div><div>&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;act</div><div><span style="color: #569cd6;">group&nbsp;by</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;WorkDate</div><div><span style="color: #569cd6;">ORDER&nbsp;BY</span>&nbsp;userId,&nbsp;workdate</div><br><div><span style="color: #6a9955;">--=========================================================================</span></div><div><span style="color: #6a9955;">--&nbsp;초단위로&nbsp;계산</span></div><div><span style="color: #6a9955;">--=========================================================================</span></div><div><span style="color: #569cd6;">SET</span>&nbsp;time_zone&nbsp;=&nbsp;<span style="color: #ce9178;">'Asia/Seoul'</span>;</div><div><span style="color: #569cd6;">SET</span>&nbsp;time_zone&nbsp;=&nbsp;<span style="color: #ce9178;">'UTC'</span>;</div><div><span style="color: #569cd6;">select</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;WorkDate,&nbsp;<span style="color: #dcdcaa;">sum</span>(WorkSeconds)&nbsp;min_activity</div><div><span style="color: #569cd6;">from</span>&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">date</span>(StartTime)&nbsp;WorkDate,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">if</span>(<span style="color: #569cd6;">date</span>(StartTime)&nbsp;=&nbsp;<span style="color: #569cd6;">date</span>(EndTime),&nbsp;time_to_sec(timediff(EndTime,&nbsp;StartTime)),&nbsp;(<span style="color: #b5cea8;">86400</span>&nbsp;-&nbsp;time_to_sec(<span style="color: #569cd6;">time</span>(StartTime))))&nbsp;WorkSeconds</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">from</span>&nbsp;tss_log</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">UNION&nbsp;ALL</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">date</span>(EndTime)&nbsp;WorkDate,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">if</span>(<span style="color: #569cd6;">date</span>(StartTime)&nbsp;=&nbsp;<span style="color: #569cd6;">date</span>(EndTime),&nbsp;<span style="color: #b5cea8;">0</span>,&nbsp;time_to_sec(<span style="color: #569cd6;">time</span>(EndTime)))&nbsp;WorkSeconds</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">from</span>&nbsp;tss_log</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">UNION&nbsp;ALL</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;&nbsp;&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vDate&nbsp;WorkDate,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #dcdcaa;">sum</span>(<span style="color: #b5cea8;">86400</span>)&nbsp;WorkSeconds</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">from</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vcm_date</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">join</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tss_log</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">on</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vcm_date.vDate&nbsp;&gt;&nbsp;<span style="color: #569cd6;">date</span>(StartTime)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">and</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vcm_date.vDate&nbsp;&lt;&nbsp;<span style="color: #569cd6;">date</span>(EndTime)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">where</span>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #dcdcaa;">datediff</span>(EndTime,&nbsp;StartTime)&nbsp;&gt;&nbsp;<span style="color: #b5cea8;">1</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">group&nbsp;by</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;vDate</div><div>&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;act</div><div><span style="color: #569cd6;">group&nbsp;by</span>&nbsp;CompanyId,&nbsp;SaasId,&nbsp;FeatureId,&nbsp;UserId,&nbsp;WorkDate</div><div><span style="color: #569cd6;">ORDER&nbsp;BY</span>&nbsp;userId,&nbsp;workdate</div></div><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p>


[MySQL & MariaDB] 시간 합치기 Merge StartTime EndTime