[LoaTodo / Spring] 로스트아크 오픈 API - 카오스 던전 수확 데이터 가져오기

1. 서론

인벤에서 괜찮은 데이터를 발견해서 웹으로 만들어보았다.

 

 

엑셀에 1200개 이상의 카던 수확 데이터가 존재해서 이걸 maria DB에 테이블을 만들어서 넣어놓았다.

테이블은 Spring JPA를 이용하였고,

데이터를 가져오는 것은 DBeavear를 사용하였다

 

 

2. 개발

2 - 1. DTO

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ChaosDungeon {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY) // 번호 증가 전략을 데이터베이스를 따라간다.
   private int id;

   @Column(nullable = false)
   @ColumnDefault("0")
   private int level;

   @Column(nullable = false)
   @ColumnDefault("0")
   private int counts;

   @Column(nullable = false)
   @ColumnDefault("0")
   private double shilling;

   @ColumnDefault("0")
   private double gold;

   @Column(nullable = false)
   @ColumnDefault("0")
   private double honorShard; //명예의 파편

   @Column(nullable = false)
   @ColumnDefault("0")
   private double leapstone; //돌파석

   @Column(nullable = false)
   @ColumnDefault("0")
   private double destructionStone; //파괴석

   @Column(nullable = false)
   @ColumnDefault("0")
   private double guardianStone; //수호석

   @Column(nullable = false)
   @ColumnDefault("0")
   private double jewelry; //1레벨 보석

   @ColumnDefault("0")
   private double cubeTicket; //큐브 티켓

   @ColumnDefault("0")
   private double hallTicket; //회랑 티켓

   @ColumnDefault("0")
   private double goldRoom; //골드방

   @ColumnDefault("0")
   private double bossRoom; //보스방
}

 

 

2 - 2. csv 데이터 옮기기

 

 

 

2 - 3. 데이터 호출

레벨별로 나누어서 통계를 구해야하는데, 하나의 로우는 1수확당이 아니므로 이것도 처리를 해줘야한다.
Native Query를 이용하여 SQL문으로 작업 후 데이터를 불러왔다.

 

[ChaosDungeonRepository]

public interface ChaosDungeonRepository extends JpaRepository<ChaosDungeon,Integer> {

   @Query(value = "SELECT id, level, "
      + "sum(counts) as counts, "
      + "ROUND(sum(shilling)/sum(counts),2) as shilling, "
      + "ROUND(sum(gold)/sum(counts),2) as gold, "
      + "ROUND(sum(honor_shard)/sum(counts),2) as honor_shard, "
      + "ROUND(sum(leapstone)/sum(counts),2) as leapstone, "
      + "ROUND(sum(destruction_stone)/sum(counts),2) as destruction_stone, "
      + "ROUND(sum(guardian_stone)/sum(counts),2) as guardian_stone, "
      + "ROUND(sum(jewelry)/sum(counts),2) as jewelry, "
      + "ROUND(sum(cube_ticket)/sum(counts),2) as cube_ticket, "
      + "ROUND(sum(hall_ticket)/sum(counts),2) as hall_ticket, "
      + "ROUND(sum(gold_room)/sum(counts),2) as gold_room, "
      + "ROUND(sum(boss_room)/sum(counts),2) as boss_room "
      + "FROM chaos_dungeon group by level", nativeQuery = true)
   List<ChaosDungeon> chaosDungeonStatisticsList();
}

 

[ChaosDungeonService]

public List<ChaosDungeon> getChaosDungeonStatistics() {
   return chaosDungeonRepository.chaosDungeonStatisticsList();
}

 

[ContentsController]

@GetMapping("/contents/ChaosDungeon/statistics")
public String chaosDungeonStatistics(Model model) {
   List<ChaosDungeon> chaosDungeonStatistics = chaosDungeonService.getChaosDungeonStatistics();
   model.addAttribute("chaosDungeonStatistics", chaosDungeonStatistics);
   return "contents/ChaosDungeon/statistics";
}

 

2 - 4. 결과

 

 

3. 정리 및 후기

• 실링은 많이 차이안난다.
• 큐브티켓은 15% 내외, 회랑티켓은 10% 내외
• 골드방 5%내외, 보스방 2%내외